Converting String Field to a Numeric Field

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

SQL Server Development (2000)

Converting String Field to a Numeric Field

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2005-01-31 : 09:24:29

hi , i saw somewhere how to do this , but i did not find in FAQ or Seach

The problems is

i have a Column with '123.544-567'
i would like to convert to 123544567

I tried Convert but not successfull

any help ?

tks
C. Lages

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-01-31 : 09:55:45

You need to get it in a format that can be converted directly to numeric. Uset the ISNUMERIC function to determine that. You could use the replace function to remove the "." and "-".

See Examples:


-- Invalid numeric (Returns 0)
select isnumeric('123.544-567')

-- Valid numeric (Returns 1)
select isnumeric('123.544567')

-- Remove "." and "-" and convert to INT
select convert(int,replace(replace('123.544-567','.',''),'-',''))

quote:
Originally posted by CLages

hi , i saw somewhere how to do this , but i did not find in FAQ or Seach

The problems is

i have a Column with '123.544-567'
i would like to convert to 123544567

I tried Convert but not successfull

any help ?

tks
C. Lages

Codo Ergo Sum

CLages
Posting Yak Master

116 Posts

Posted - 2005-01-31 : 11:04:47

Your Solution Works, but in this string could be any character
like @,A,B,C etc then i Did this and works fine

if anybody wants improve , will be better for me,

tks
Carlos Lages

declare @ind int
set @ind = 1
declare @New char(20)
set @new = ' '
declare @Insc char(20)
set @insc = '123.456-789.01'

WHILE @ind < 21
BEGIN
if SUBSTRING(@insc , @ind, 1) in ('0','1','2','3','4','5','6','7','8','9')
BEGIN
SET @new = rtrim(@new) + SUBSTRING(@insc , @ind, 1)
print @new
END
set @ind = @ind + 1
END

print + @new

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-01-31 : 23:34:01

Looks OK. You may find it more convenient to turn that code into a function if you have to do that a lot, or use it in different places.

quote:
Originally posted by CLages

Your Solution Works, but in this string could be any character
like @,A,B,C etc then i Did this and works fine

if anybody wants improve , will be better for me,

tks
Carlos Lages

declare @ind int
set @ind = 1
declare @New char(20)
set @new = ' '
declare @Insc char(20)
set @insc = '123.456-789.01'

WHILE @ind < 21
BEGIN
if SUBSTRING(@insc , @ind, 1) in ('0','1','2','3','4','5','6','7','8','9')
BEGIN
SET @new = rtrim(@new) + SUBSTRING(@insc , @ind, 1)
print @new
END
set @ind = @ind + 1
END

print + @new

Codo Ergo Sum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources