Validating if a varchar column contains int values
-
Hi, I have a column that contains varchar values, the structure that the values are built is always the same, first six characters is a string prefix, anything after the prefix must be int or it is invalid. What I want to do is subtring the column starting at character 7 for the rest of the value, but how do I validate if that value returned by the substring is int. something like if value is int, is there a way that I can do this? So lets say the column contains the following values: abcdef123321 abcdef78998745 abcdef4568547 abcdef123548/ it should only return the first 3 rows because the fourth row is invalid because of the "/" at the end Thanks in advance
No matter how long he who laughs last laughs, he who laughs first has a head start!
-
Hi, I have a column that contains varchar values, the structure that the values are built is always the same, first six characters is a string prefix, anything after the prefix must be int or it is invalid. What I want to do is subtring the column starting at character 7 for the rest of the value, but how do I validate if that value returned by the substring is int. something like if value is int, is there a way that I can do this? So lets say the column contains the following values: abcdef123321 abcdef78998745 abcdef4568547 abcdef123548/ it should only return the first 3 rows because the fourth row is invalid because of the "/" at the end Thanks in advance
No matter how long he who laughs last laughs, he who laughs first has a head start!
You can use the sql ISNUMERIC
declare @variable varchar(100) set @variable = 'abcdef78998745' select isnumeric(substring(@variable,7,datalength(@variable)-6)) --returns 1 set @variable = 'abcdef4568547' select isnumeric(substring(@variable,7,datalength(@variable)-6)) --returns 1 set @variable = 'abcdef123548/' select isnumeric(substring(@variable,7,datalength(@variable)-6)) --returns 0 as not numericHope this helpsBob Ashfield Consultants Ltd
-
You can use the sql ISNUMERIC
declare @variable varchar(100) set @variable = 'abcdef78998745' select isnumeric(substring(@variable,7,datalength(@variable)-6)) --returns 1 set @variable = 'abcdef4568547' select isnumeric(substring(@variable,7,datalength(@variable)-6)) --returns 1 set @variable = 'abcdef123548/' select isnumeric(substring(@variable,7,datalength(@variable)-6)) --returns 0 as not numericHope this helpsBob Ashfield Consultants Ltd
-
Hi, I have a column that contains varchar values, the structure that the values are built is always the same, first six characters is a string prefix, anything after the prefix must be int or it is invalid. What I want to do is subtring the column starting at character 7 for the rest of the value, but how do I validate if that value returned by the substring is int. something like if value is int, is there a way that I can do this? So lets say the column contains the following values: abcdef123321 abcdef78998745 abcdef4568547 abcdef123548/ it should only return the first 3 rows because the fourth row is invalid because of the "/" at the end Thanks in advance
No matter how long he who laughs last laughs, he who laughs first has a head start!
-
I think this will do the trick, thanks a million.
No matter how long he who laughs last laughs, he who laughs first has a head start!