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
-
2Possible duplicate of Remove all non numeric characters in sql SELECTRealCheeseLord– RealCheeseLord2017-08-23 13:11:41 +00:00Commented Aug 23, 2017 at 13:11
-
1Is using function banned in your work environment? Why don't you use?Abdullah Nehir– Abdullah Nehir2017-08-23 13:17:14 +00:00Commented Aug 23, 2017 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 goodEmma W.– Emma W.2017-08-23 13:19:24 +00:00Commented Aug 23, 2017 at 13:19
-
What is the idea? If you put it in here, someone may improve the idea.Abdullah Nehir– Abdullah Nehir2017-08-23 13:20:18 +00:00Commented Aug 23, 2017 at 13:20
-
stackoverflow.com/a/17950550/6787667Emma W.– Emma W.2017-08-23 13:21:52 +00:00Commented Aug 23, 2017 at 13:21
|
Show 1 more comment
2 Answers
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
1 Comment
Emma W.
Wow, thank you very much! Can you explain what's happening in query. I really want to understand this
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.
1 Comment
Emma W.
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