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

T-SQL Output Query | The Computed Column Puzzle

Recently I was implementing something in my project with computed column. From there this question comes to my mind. So here is the question. Let’s say you are doing some calculation on current year basis. Now what will happen next year means when the system dates change. Check out the example below-

--

--Consider the table definition
CREATE TABLE testCalculateColumn
(
	 ID SMALLINT
	,NumberOfYears SMALLINT
	,StartingYear AS YEAR(GETDATE()) - NumberOfYears
)
GO

--

Here Starting year is the computed column which is dependent of NumberOfYears and the current year. Now NumberOfYear will be static once we inserted any row, or it will only change if we update any value, But YEAR(GETDATE())can change every year based on your system date. Now let’s insert values into the table.

--

--Insert some values
INSERT INTO testCalculateColumn
VALUES (1,2),(1,8),(1,12),(1,1),(1,5)
GO

--

Now let’s run the select command to check the values of Starting Year

--

SELECT 
	ID,NumberOfYears, StartingYear
FROM
	testCalculateColumn;
GO


--

Output when Year = 2015

Today the year is 2015, Now what will happen next year when you system year = 2016. Are values of Starting year will change or will remain the same?

You have fill the below table when system year changes from 2015 to 2016-

Please leave a comment if you need solution to the above puzzle ! Cheers

Keep Learning

Pawan Kumar Khowal

MSBISKills.com