How can I bring back just the numbers in a field 3

[scc]

I have this query that needs to convert a string field that is suppose to contain an SSN to an int, but it is failing because there is data in there where the last character is alpha. Example: 123456789N (Bad db design in my opinion, but I have to live with it).

Is there a way I convert this to an integer and just return 123456789. I don't want to assume that the last character is always the problem. For example, should there be a SSN that says 12345X6789 then I would like to return 12345, like using the Val function in VB.

Any ideas how I can do this in SQL? I'm using SQL Server 2000.

Thanks.

 

[maswien]

Assume you have a table 'test1'

select * from test1

cid cname cint
--- --------------------------- -----
1 ahgh1212312dx12367mhhj1212 null
2 ahgh12132dx12355567mhhj12sd772676 null
3 ahgh1213242dx12hhj12wsds213d77626 null

I assume you want get the logest number sequence in each
cname as the SSN and write it to cint, try following SQL:


declare @i as int
declare @j as int
declare @c as int
declare @str1 as varchar(256)
declare @str2 as varchar(256)
declare @flag as bit
declare @maxint as int

set @i = 1
set @j = 1
set @maxint = 0
set @flag = 0
set @str2 = ''
select @c = max(cid) from test1

while @i <= @c
begin
select @str1=cname from test1 where cid = @i

while (@j <= len(@str1))
begin

if (ascii(substring(@str1, @j ,1)) > 57) or (ascii(substring(@str1, @j ,1)) < 48)
begin
if (@flag = 1)
begin
if (cast(@str2 as int) > @maxint)
begin
set @maxint = cast(@str2 as int)
end
set @str2 = ''
end
set @flag = 0
end

else
begin
set @flag = 1
set @str2 = @str2 + substring(@str1, @j ,1)
if (@j = len(@str1))
begin
if (cast(@str2 as int) > @maxint)
begin
set @maxint = cast(@str2 as int)
end
set @str2 = ''
end

end
set @j = @j + 1

end

update test1 set cint = @maxint where cid = @i

set @i = @i + 1
set @maxint = 0
set @j = 1
end

 

[scc]

Thanks for your quick reponse!

 

[JamesLean]

Just a couple of other options for you. If you just want the first numbers (ie up to the first non-numeric character) then you can use:

SELECT CASE WHEN ISNUMERIC(col1) = 1 THEN col1
  ELSE LEFT(col1, PATINDEX('%[^0-9]%', col1) - 1) END
FROM tbl

Or you could use this function which strips out all non-numeric characters from a value:

CREATE FUNCTION dbo.fnMakeNumeric(@val varchar(30))
	RETURNS varchar(30)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%', @val) > 0
BEGIN
	SET @val = STUFF(@val, PATINDEX('%[^0-9]%', @val), 1, '')
END

RETURN @val
END
GO

--James

 

[scc]

Thanks! I actually may need both ways.

Just curious what the caret means in this expression:
'%[^0-9]%'

I had wondered if you could do a range in a like statement, for example A-Z so that answers this question as well. Does the caret mean 'between' by any chance?

 

[JamesLean]

The caret means NOT. The dash(-) is how you specify a range. So that is basically finding the first character that is NOT a digit between 0 and 9, ie a non-numeric character.

--James

 

[scc]

Great! I learned more than I set out to. This will be very helpful!!

 

[JamesLean]

If you look up LIKE in the Books Online index and go to &quot;Pattern Matching in Search Conditions&quot; you can see all the different things you can use to search for patterns. It can be quite a useful technique!

--James

 

[scc]

Appreciate the tip!

 

[nigelrivett]

see

http://www.nigelrivett.net/RemoveNonNumericCharacters.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.