Help to get number from stringg values

Question

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

SANAKAMAR19410102
SmithH289222

Desired output:
19410102
289222

Answer 1

Another way:

https://sqlzealots.com/2017/05/28/how-to-extract-only-numbers-from-a-string-in-sql-server/

Answer 2

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

Answer 3

Hi @M Jugnu ,

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

Answer 4

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  
  

Answer 5

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   |
+----+-------------------+----------+