T-SQL code to parse Numbers from a String

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

 

We can also write function to encapsulate this code and use wherever required.

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

T-SQL Puzzle 4 - Can we rollback after TRUNCATE?

You know that TRUNCATE is DDL command and we can't rollback DDL commands as per BOL.
So what will be the output of below query:

CREATE TABLE Test(Col int)
GO

INSERT INTO Test (Col)
VALUES (1), (2), (3)

SELECT * FROM Test

BEGIN TRAN
   TRUNCATE TABLE Test
   SELECT * FROM Test
ROLLBACK TRAN
SELECT * FROM Test

DROP TABLE Test

T-SQL Puzzle 3 - Syntax Behaviour

What will be the out of below SELECT statements:

SELECT 1.Columns
SELECT 1.ColumnName
SELECT (1.)ColumnName
SELECT '1.'ColumnName
SELECT 1.#ColumnName
SELECT $1.ColumnName
SELECT 1.[ a%^& .ColumnName]

Has anyone seen this behavior?
Do you have any idea what's happening?

T-SQL Puzzle 2 - CASE Statement

What will be the output of below Query:

DECLARE @f as int

SET @f = 1
SELECT CASE 1
    WHEN @f THEN 'First'
    WHEN @f THEN 'Second'
    ELSE 'Nothing' END
AS CaseStmt

Below are the options:
1. First
2. Second
3. Nothing
4. No records
5. syntax Error

T-SQL Puzzle 1: WHERE & GROUP BY

What will be the output of query given below. There is no FROM clause and no GROUP BY clause.

Does it throw an error?
Do you get any resultset?
Do you get a single row containing NULL?
Do you get a single row with 'Something' data?
Do you get multiple rows?
Cheers!


SELECT 'Something'
WHERE 1=2
HAVING 1=1

Note: You can easily check your answer by running the query but please check your skills before you do so.