I have a string in form "abcdefg 12355 ijklmn"

Now I want get only the number 12355 within the string !!
Is there any function available in T-SQL of Sql server 2K??

Thanks
for any help

I dont think there is any off the shelf function. You would need to write your own custom function. SQL Server is not really good at string manipulations. You could do this very easily using regular expressions at 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 the ISNUMERIC function.  This function will also return a 1 for some characters 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's would.  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.