ParseInt for SQL Server? 2

[adam0101]

Is there a simple way to extract numbers from a string? For example, I'd like to get "329" from "NW329F".

Thanks,

Adam
while(ignorance){perpetuate(violence,fear,hatred);life=life-1};

 

[vongrunt]

Make your own function, for example:

create function get1stNumber( @s varchar(64) )
returns varchar(16)
as
begin
declare @ret varchar(16)
declare @pos tinyint

set @pos = patindex('%[0-9]%', @s)
if @pos > 0
begin
	set @s = right( @s, len(@s)-@pos+1)
	set @pos = patindex('%[^0-9]%', @s)
	if @pos = 0 set @pos = len(@s) + 1
	set @ret = left(@s, @pos-1)
end
return( @ret )
end
go
...
select dbo.get1stNumber('NW329F')

 

[adam0101]

I was hoping there'd be a more elegant method, but at the end of the day a working solution is better than none at all.

Thanks for your help.

Adam
while(ignorance){perpetuate(violence,fear,hatred);life=life-1};

 

[zhavic]

This will work, if you have just one number in each string:

SELECT SUBSTRING( string, PATINDEX( '%[0-9]%', string ), LEN( string ) - PATINDEX( '%[0-9]%', string ) - PATINDEX( '%[0-9]%', REVERSE( string ) ) + 2 )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.