September 12, 2002 at 12:45 pm
Can somebody help in converting varchar to int.
there is a varchar field in the database containing value '2000 ' and I wanted to convert it into integer.
I am getting an error while using convert function , I think there might me an special character in the filed.
Is there any way to remove those special character in the field .
appreciated.
September 12, 2002 at 2:17 pm
Here is one way if you have the field that contains non-numberic data in a local variable. Isn't very short. Probably is a better method.
set @string = '2000' + char(0)
set @i = 0
while (@I < len(@string))
begin
set @i = @i + 1
if substring(@string,@I,1) < '0' or
substring(@string, @I,1) > '9'
begin
set @string = substring(@string,1,@I-1) + substring(@string,@i+1,len(@string)-@i)
set @I = @I - 1
end
end
select cast(@string as int)
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 12, 2002 at 2:56 pm
You are the man.
Thanks buddy.
September 12, 2002 at 3:17 pm
If I use this in a float data type I get confusing result.
Thanks
September 12, 2002 at 3:44 pm
I have solved the problem. I have created a function based on your script which will solve the decimal problem.
CREATE FUNCTION StrToNumber(@string varchar(100))
RETURNS varchar(100)
AS
BEGIN
declare @i int
declare @location int
declare @stringf varchar(100)
declare @strvar varchar(100)
set @strVar = @string
set @string = @string + char(0)
set @i = 0
while (@I < len(@string))
begin
set @i = @i + 1
if substring(@string,@I,1) < '0' or substring(@string, @I,1) > '9'
begin
set @string = substring(@string,1,@I-1) + substring(@string,@i+1,len(@string)-@i)
set @I = @I - 1
end
if patindex('%.%',@strVar) > 0
begin
set @location = patindex('%.%',@strVar)
set @stringf = STUFF(@string, @location, 0, '.')
end else
set @stringf = @string
end
return @stringf
END
Thanks a lot for your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply