how to tell if data is an int value?
-
i am writing a query to parse some data out of a varchar field which contains product names. i am pulling out everything that is after a '-' in the field. for some products, this is a color name, but for others it is a part number (all numbers). is there a way that i can do a check to see if the value COULD be converted to int, so that i can separate the colors from the part numbers?
hope this makes sense! thanks. 🙂
-
Hi,
not sure if this is what you want
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
DECLARE @i INT
DECLARE @sz1 VARCHAR(50)
DECLARE @vNIL CHAR(1)
DECLARE @bool BIT
--SET @sz1 = 'b'
SET @sz1 = '1' --for testing and proof of concept
SET @i=1
SET @bool=0
WHILE (@i<=LEN(@sz1))
BEGIN
SELECT @vNIL=SUBSTRING(@sz1,@i,1)
IF (@vNIL<='9') AND (@vNIL>='0') OR (@vNIL='-')
SELECT @bool=1
SELECT @i=@i+1
END
SELECT @bool
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOhttp://www.insidesql.de/scripts/sqlserverfaq.php?id=83
Frank
Edited by - Frank Kalis on 10/09/2003 07:35:31 AM
Edited by - Frank Kalis on 10/09/2003 07:36:50 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
WHERE ISNUMERIC(STUFF(ColName,1,CHARINDEX('-',ColName),'')) = 1--Jonathan
--Jonathan -
quote:
WHERE ISNUMERIC(STUFF(ColName,1,CHARINDEX('-',ColName),'')) = 1oops, sorry, haven't read that the string after '-' seem to contain only numbers.
However, if not like this
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
DECLARE @i INT
DECLARE @sz1 VARCHAR(50)
DECLARE @vNIL CHAR(1)
DECLARE @bool BIT
--SET @sz1 = 'b'
SET @sz1 = 'a5xo3z1' --for testing and proof of concept
SET @i=1SET @bool=0
WHILE (@i<=LEN(@sz1))
BEGIN
SELECT @vNIL=SUBSTRING(@sz1,@i,1)
IF (@vNIL<='9') AND (@vNIL>='0') OR (@vNIL='-')
SELECT @bool=1
SELECT @i=@i+1
END
SELECT @bool
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SELECT ISNUMERIC(STUFF('a5xo3z1',1,CHARINDEX('-','a5xo3z1'),''))both solution have different results
Once again, it depends.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank--
Yes, we seem to have interpreted the question differently.
Couldn't your loop be replaced by just a set-based solution?
WHERE @sz1 LIKE '%[0-9]%'Or, more to the point of the question:
WHERE STUFF(@ColName,1,CHARINDEX('-',@ColName),'') LIKE '%[0-9]%'--Jonathan
--Jonathan -
quote:
Yes, we seem to have interpreted the question differently.I think the question was to evaluate if a string can be converted to int. The answer to such a question is yes or no.
Actual extraction and conversion ?!? is different and depends on what the string looks like.
If I read the question right, your solution works fine as all numbers are in consecutive positions. However, if they are not, we must find another solution.
That what I meant with 'It depends'.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
After thinking one night over it (and, of course, your mail, Jonathan!) I must admit, I stand corrected.
The solution I've posted is not the best one for this given scenario.
In fact, it only checks whether the string contains a number at all. It does not extract this number nor is it optimal implemented nor is the quick modifications I've made to the original routine errorfree.
So,I suggest the original questioner to go with Jonathan's solution!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Since isNumeric returns a 1 for any REAL number and the original question was "how do I test for an INTEGER" you would need to use the following test
where isnumeric(substring(testField, charindex('-', testField), datalength(testfield))) = 1
and charindex('.', substring(testField, charindex('-', testField), datalength(testfield))) = 0
I'm not sure what the limit is on the size of the string that IsNumeric will evaluate. I tested it on a string of 250 numbers, that should be sufficient to cover any number that you could want to use.
-
quote:
Since isNumeric returns a 1 for any REAL number and the original question was "how do I test for an INTEGER" you would need to use the following testwhere isnumeric(substring(testField, charindex('-', testField), datalength(testfield))) = 1
and charindex('.', substring(testField, charindex('-', testField), datalength(testfield))) = 0
I'm not sure what the limit is on the size of the string that IsNumeric will evaluate. I tested it on a string of 250 numbers, that should be sufficient to cover any number that you could want to use.
If you really believe this is what the OP wants (and I certainly don't, based on his saying the value will be "all numbers" and the logical assumption that the values are concatenated with valid int part numbers), won't you also need to make sure that the value is within the bounds of an int? In fact, this is a valid concern even if the value is all numbers... After all, he also writes that he needs a "check to see if the value COULD be converted to int," so your test is unnecessary but the bounds test would be.
WHERE CASE
WHEN ISNUMERIC(STUFF(ColName,1,CHARINDEX('-',ColName),'')) = 1
AND CHARINDEX('.',STUFF(ColName,1,CHARINDEX('-',ColName),'')) = 0
THEN CASE
WHEN CAST(STUFF(ColName,1,CHARINDEX('-',ColName),'') AS float) BETWEEN POWER(-2,31) AND POWER(2.,31) - 1
THEN 1
ELSE 0 END
ELSE 0 END = 1So all your testing with long strings of numbers is also moot.
--Jonathan
--Jonathan -
Much ado about nothing ?!?
Hopefully correct, in German
'Viel Lärm um nichts!'
Before guessing any further we should let the original questioner let himself explain if the question is already answered or not.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply