T-SQL Output Query | [ The String & Null Puzzle ]

Please go through the sample queries involved substring and reasons behind the outputs and errors..

--

DECLARE @str AS VARCHAR(50)

SET @str = 'Pawan Kumar - A Developer'

SELECT SUBSTRING(@str, NULL, 5 )

--O/P - NULL
--If you provide null in place of starting location , it will return NULL.


SELECT SUBSTRING(@str, -1, 5 )

--O/P - Paw
--Here we are starting from -1, So it will skip -1,0 location and Start from first location till 3rd position since we don't have characters at those locations

SELECT SUBSTRING(@str, 6, 5 )

--O/P -  Kuma
--Here we are starting from -1, So it will Start from 6th location and will pick 5 characters

SELECT SUBSTRING(@str, NULL, NULL )

--O/P - NULL
--If everything is NULL, then it will return NULL 🙂

SELECT SUBSTRING(@str, 0, 5 )

--O/P - Pawa
--Here we are starting from 0, So will Start from first location till 5th position will pick 4 characters only.

SELECT SUBSTRING(@str, NULL, -1 )

--O/P
Msg 536, Level 16, State 1, Line 11
Invalid length parameter passed to the substring function.

--

Cheers, Keep Learning !!

Pawan Kumar Khowal

MSBISKills.com