Get only the number from a string, T-SQL??

I have astring in form "abcdefg 12355 ijklmn"
Now I want get only thenumber 12355 within thestring !!
Is there any function available in T-SQL of Sql server 2K??
Thanks
for any helpI dont think there is any off the shelf function. You would need towrite your own custom function. SQL Server is not really good at stringmanipulations. You could do this very easily using regular expressionsat the front end.
|||

CREATE FUNCTION dbo.fFilterNumeric
(
@.Src nvarchar(255)
)
RETURNS nvarchar(255)
AS
BEGIN
declare @.Res nvarchar(255)
declare @.i int, @.l int, @.c char
select @.i=1, @.l=len(@.Src)
SET @.Res = ''
while @.i<=@.l
begin
set @.c=upper(substring(@.Src,@.i,1))
IF isnumeric(@.c)=1
SET @.Res = @.Res + @.c
set @.i=@.i+1
end

return(@.res)
END

|||The issue I have with IndianScorpion's suggestion is the use of theISNUMERIC function. This function will also return a 1 for somecharacters you'd might not expect, such as a comma (,), dollar sign($), and a period (.).
Try this little script in Query Analyzer to see the ASCII characters between 1 and 254 that SQL Server considers to be numeric:

DECLARE @.myTest char(10), @.a int
SELECT @.a = 1
WHILE @.a < 255
BEGIN
SELECT @.myTest = CHAR(@.a)
IF ISNUMERIC(@.myTest) = 1
PRINT CAST(@.a AS char(3)) + ' -- ' + @.myTest
SELECT @.a = @.a + 1
END

I would do something like this instead:

IF @.c IN ('0','1','2','3','4','5','6','7','8','9')

|||dont like long cycles,
if needs cut just one number without dot (by Ukrainian - крапки),
and number always exists,
try example below, this just example, may this example transform like one select
--
Declare @.X varchar(100)
Select @.X= 'Here is where15234Numbers'
--
Select @.X= SubString(@.X,PATINDEX('%[0-9]%',@.X),Len(@.X))
Select @.X= SubString(@.X,0,PATINDEX('%[^0-9]%',@.X))
--// show result
Select @.X

|||

pmz0178 wrote:

Declare @.X varchar(100)
Select @.X= 'Here is where15234Numbers'
--
Select @.X= SubString(@.X,PATINDEX('%[0-9]%',@.X),Len(@.X))
Select @.X= SubString(@.X,0,PATINDEX('%[^0-9]%',@.X))
--// show result
Select @.X

The limitation of this solution is that a value of @.X like this:

Select @.X= 'Here is where15234Numbers987'

will result in 15234, and not 15234987, as IndianScorpion'swould. This could be a positive thing or a negative thing,depending on the situation.
|||I suggest that TSQL is the wrong tool for the job, at least until you can use the CLR. IMO much better if you can use a client to do this work before it gets to the database, a simple (well when are they) regular expression would sort this kind of problem out.