Need number part of a string value

[mapeirce]

I have a need to extract the number part only from a string that may contain text as well as the number.

example 809A. Is there a function that will return just the 809 in the form of an int? or even just the 809? (I can cast the result to int if needed) I don't know what the length of the string or the number might be. it could vary.

Thanks for any help

map

 

[sunila7]

Try this...

SELECT REVERSE(SUBSTRING(REVERSE('809A'),2,LEN('809A')))


Sunil

 

[rac2]

See Books Online topics SUBSTRING, PATINDEX, and REPLACE.

SELECT SUBSTRING(Room, 1, (PATINDEX('%[^0-9]%', Room) - 1))

will yield results similar to Javascript function parseInt(Room).

 

[JamesLean]

The complicated bit here is that you don't know how long the number and string sections will be. Can I assume there is always a number followed by a string? If so then this should work:

SELECT LEFT(val, PATINDEX('%[^0-9]%', val) - 1)

--James

 

[maswien]

-- go to database master, create a function

CREATE FUNCTION getnumber (@str varchar(256))
RETURNS int
AS
BEGIN
DECLARE @getnum int
declare @cnt int
declare @ch as varchar(256)
set @cnt = 1
set @ch = ''
while (@cnt < len(@str) + 1 )
begin
if ( ascii(substring(@str,@cnt,1)) > 48 and ascii(substring(@str,@cnt,1)) < 57 )
begin
set @ch = @ch + substring(@str,@cnt,1)
end
set @cnt = @cnt + 1
end

set @getnum = cast(@ch as int)

RETURN(@getnum)

END

-- test this function

select master.dbo.getnumber( 'asjj1234sadasd')