Converting a string to an int - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting a string to an int

Author

Topic

stannius
Starting Member

10 Posts

Posted - 2004-03-04 : 13:39:52

I have a table with a colum in it like this:


TripID   NumDays
XXX      '9 days/8 nights'
XXX      '10-11 Days'
XXX      '1,2, or 3 days'
XXX      '8'
XXX      '129 Luxurious days'

I want to search on the Numdays column similar to this:
SELECT TripID
FROM tblTrips
WHERE NumDays BETWEEN 10 and 15

Obviously, that raises an error on all but the records with just a number in NumDays. I wrote a function to do a conversion...


CREATE FUNCTION fnSafeConvertVarchar2Int (@STR VARCHAR(50))
RETURNS INT 
AS
BEGIN
	DECLARE @i int
	set @i = 1 
	
	WHILE @i <= LEN(@STR)
	BEGIN
		IF(NOT ISNUMERIC(SUBSTRING(@STR, 1, @i)) = 1) 
		    break
		set @i = @i + 1
	END
	RETURN SUBSTRING(@STR, 1, @i - 1)
END

... but the function fails on '1,2 or 3 days'. Apparently '1,2' is counted as numeric (it would be equal to 1.2 in Europe I guess).

Alternately, is there some way to catch the errors generated by the conversion? So I could do


WHILE (@@ERROR = 0 AND @i < LEN(@foo)) 
BEGIN
	SELECT @result = CAST(SUBSTRING(@foo,1,@i) AS int)
	set @i = @i + 1
END

so as soon as it his some substring it couldn't handle, it would return the result of the last successful conversion.

Thanks for the help,
steve

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-04 : 15:06:25

I'm a bit comfused here but if the comma is the only thin causing you problems then a simple

SET @STR = (SELECT REPLACE((@STR, ',', '|'))

would do th trick. The comfusing part is how you'll translate 98 to 9 days/8 nights or 123 to '1,2, or 3 days' but I guess I shouldn't worry about that ;)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

stannius
Starting Member

10 Posts

Posted - 2004-03-04 : 15:21:14

The NumDays column already exists, with strings in it of a variety of formats as shown above. I want to turn those strings into numbers so i can compare them to other numbers in the where clause. So instead of replacing the comma, i would want to convert everything before that comma into a number...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-04 : 15:34:20

Well, I might be totally off here but I thaught you said the comme between 1 and 2 was interpreted as a decimal-delimiter hence my suggestion to replace it with something that for sure would not be interpreted as something numeric...

stannius
Starting Member

10 Posts

Posted - 2004-03-04 : 16:01:11

Oh, I see, that's cool. Is there a built in function that can do this conversion (not the replacement of the commas, but the actual conversion) or do I have to make my own here?

Another option to avoid the commas is instead of using the built-in ISNUMERIC I could instead use
ASCII(@char) >= ASCII('0') AND ASCII(@char) <= ASCII('9')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources