Someone asked me recently about T-SQL function to parse numbers from a string. Since there is no built-in function as such, I explained him the logic to calculate same thing using T-SQL code. I will explain the logic here, in case you need to implement same thing (or somewhat similar thing) in your project.
Let’s take example to understand the problem.
Input
|
Output
|
Hari12 Sharma34
|
1234
|
My 1st code is D$34A and 2nd code is E#078!!!
|
1342078
|
Hari# Sharma
| |
$1#0?0 !?@1
|
1001
|
Here is the code to get the desired output:
DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'
DECLARE @Position SMALLINT = 0
SET @Position = PATINDEX('%[^0-9]%', @Str) --Find first character
WHILE (@Position > 0)
BEGIN
-- Replace alphabet with empty string.
SET @Str = STUFF(@Str, @Position, 1, '')
-- Find next alphabet
SET @Position = PATINDEX('%[^0-9]%', @Str)
END
SELECT @Str Result
Result
-------------------------------------
1342078
This above code can be optimized by using Tally table mentioned below:
DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'
DECLARE @Result VARCHAR(100)
SELECT @Result = COALESCE(@Result, '') + Digit
FROM (
SELECT SUBSTRING(@Str, t.ID, 1) AS Digit
FROM dbo.Tally t
WHERE t.ID <= LEN(@Str)
AND PATINDEX('%[0-9]%', SUBString(@Str, t.ID, 1)) = 1
) stg
SELECT @Result Result
Result
-------------------------------------
1342078
