SELECT only numeric without function in sql

Question

I need sql query WITHOUT FUNCTION with SELECT only numeric characters.

For example, I have in sql table 0f-gh 14-2t-4 /// and I want get this -> 01424. How I can do it with sql query SELECT, without anything, only with SELECT

Possible duplicate of [Remove all non numeric characters in sql SELECT](https://stackoverflow.com/questions/45838566/remove-all-non-numeric-characters-in-sql-select) — RealCheeseLord, Aug 23 '17 at 13:11
Is using function banned in your work environment? Why don't you use? — Abdullah Nehir, Aug 23 '17 at 13:17
I have function and it's work, but my task it without function, only with select and I have only one idea and this isn't good — Emma W., Aug 23 '17 at 13:19
What is the idea? If you put it in here, someone may improve the idea. — Abdullah Nehir, Aug 23 '17 at 13:20
This answer might solve your problem. It uses common table expressions and xml query, Kev Riley's answer: https://ask.sqlservercentral.com/questions/6518/help-using-replace-with-wildcard-matching-pattern.html — Abdullah Nehir, Aug 23 '17 at 13:44

John Cappelletti · Answer 1 · 2017-08-23T16:49:43.907

2

Here is an inline approach

Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values
(1,'0f-gh 14-2t-4 ///')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select NewValue = (Select substring(A.SomeCol,N,1) 
                                    From (Select Top (len(A.SomeCol)) N=Row_Number() Over (Order By (Select NULL)) From  master..spt_values n1) S 
                                    Where substring(A.SomeCol,N,1) like '[0-9]%'
                                    Order By N 
                                    For XML Path (''))
             ) B

Returns

ID  NewValue
1   01424

Note: Use Outer Apply if you want to see null values in the event where the string has NO numerics.

edited Aug 23 '17 at 16:49

answered Aug 23 '17 at 13:54

John Cappelletti

79,615
7
44
66

Thank you very much!!! It's work. But Your decision does not work correctly in the production database, but it works correctly in a test database. Thanks for your decision, but I chose the other solution right, because it works correctly on two databases. This only means that our working database is more than strange and it's in ODBC. My tests I did in SSMS and everything was great – Emma W. Aug 24 '17 at 09:22

score 2 · Accepted Answer · answered Aug 23 '17 at 14:44

This is the logic from digitsonlyEE which is the fastest T-SQL based "digits only" function available today.

declare @table table (somestring varchar(50));
insert @table VALUES('abc123xxx555!!!999'),('##123ttt999'),('555222!');

SELECT *
FROM @table t
CROSS APPLY 
(
  SELECT DigitsOnly =
  (
    SELECT SUBSTRING(t.somestring,n,1)
    FROM 
    (
      SELECT TOP (LEN(ISNULL(t.somestring,CHAR(32)))) 
        (CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))))
      FROM 
        (VALUES ($),($),($),($),($),($),($),($),($),($)) a(x),
        (VALUES ($),($),($),($),($),($),($),($),($),($)) b(x),
        (VALUES ($),($),($),($),($),($),($),($),($),($)) c(x),
        (VALUES ($),($),($),($),($),($),($),($),($),($)) d(x)
    ) iTally(n)
    WHERE ((ASCII(SUBSTRING(t.somestring,N,1)) - 48) & 0x7FFF) < 10
    FOR XML PATH('')
  ) 
) digitsOnlyEE(digitsOnly);

Results:

somestring            digitsOnly
--------------------- ----------
abc123xxx555!!!999    123555999
##123ttt999           123999
555222!               555222

Wow, thank you very much! Can you explain what's happening in query. I really want to understand this — Emma W., Aug 24 '17 at 08:29

SELECT only numeric without function in sql

2 Answers2