question

MJugnu-2841 avatar image
0 Votes"
MJugnu-2841 asked YitzhakKhabinsky-0887 commented

Help to get number from stringg values

Hi , How I can get the number ONLY from the following string.

SANAKAMAR19410102
SmithH289222

Desired output:
19410102
289222

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any updates?
Please also remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

If the number is always at the end of the string, then

 Declare @Sample Table(MyData varchar(50));
 Insert @Sample(MyData) Values
 ('SANAKAMAR19410102'),
 ('SmithH289222');
    
 Select Substring(MyData, PATINDEX('%[0-9]%', MyData), Len(MyData))
 From @Sample;

Tom

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @MJugnu-2841,

The following user-defined function can return a number at any position in a string:

 create function dbo.Getnum(@str nvarchar(100))
   returns bigint
 as
 begin
    while patindex('%[^0-9]%',@str)>0
   begin
   set @str=stuff(@str,patindex('%[^0-9]%',@str),1,'')
   end
   return convert(bigint,@str)
 end

Apply:

         create table #test(string varchar(25))
         insert into #test values('SANAKAMAR19410102'),('SmithH289222'),('12Smi45thH289222')
         select * from #test
            
         select dbo.Getnum(string) as result from #test

Output:

 result
 19410102
 289222
 1245289222


If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo


Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The create statement of the function dbo.Getnum must be executed separately.

Echo

0 Votes 0 ·
JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

You can do this with an inline-table valued function which will probably perform better:

  Create Function [dbo].[fnRemoveCharacters] (
         @inputString varchar(8000)
         )
 Returns Table
    With schemabinding
      As
  Return
    
   With t(n)
      As (
  Select t.n 
    From (
  Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
       , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
         )
       , iTally (n)
      As (
  Select Top (len(@inputString)) 
         checksum(row_number() over(Order By @@spid)) 
    From t t1, t t2, t t3
         )
  Select v.inputString
       , outputString = (Select substring(v.inputString, it.n, 1)
                           From iTally                     it
                          Where substring(v.inputString, it.n, 1) Like '[0-9]'
                            For xml Path(''), Type).value('.', 'bigint')
    From (Values (@inputString)) As v(inputString);
      Go

You can then use it as follows:

  Declare @testTable Table (MyData varchar(50));
   Insert Into @testTable (MyData)
   Values ('SANAKAMAR19410102')
        , ('SmithH289222');
    
  Select *
    From @testTable tt
   Cross Apply dbo.fnRemoveCharacters(tt.MyData) t
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Here is a solution for SQL Server 2017 onwards.

SQL

 -- DDL and sample data population, start
 DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [before] VARCHAR(50));
 INSERT INTO @tbl([before]) VALUES
 ('SANAKAMAR19410102'),
 ('SmithH289222');
 -- DDL and sample data population, end
    
 SELECT *
 , (SELECT STRING_AGG(value, '')
             FROM STRING_SPLIT(CAST(CAST(CAST([before] AS NVARCHAR) AS VARBINARY(50)) AS VARCHAR(50)), CHAR(0)) 
  WHERE TRY_CAST(value AS TINYINT) IS NOT NULL
             ) AS [After]
 FROM @tbl;

Output

 +----+-------------------+----------+
 | ID |      before       | After    |
 +----+-------------------+----------+
 |  1 | SANAKAMAR19410102 | 19410102 |
 |  2 | SmithH289222      | 289222   |
 +----+-------------------+----------+
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLZealots avatar image
1 Vote"
SQLZealots answered YitzhakKhabinsky-0887 commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@SQLZealots,
Good answer, +1 from my side!

0 Votes 0 ·