Advertisements

Archive

Archive for August, 2011

SQL Trivia – Reverse a string without using T-SQL REVERSE() function

August 18, 2011 8 comments

Everyone knows that the REVERSE() function (as its name suggests) reverses a string’s value, MSDN link.

This post’s title talks about going the other way, but why? If we already have this function then why to reinvent the wheel.

But at times an Interviewer may trick you and asks this question randomly: “How will you reverse a string without using REVERSE() function? No loop, should be a single query.” People who have actually worked on CTEs and know about recursive CTEs can guess the answer and create the query on-spot. Actually he wants to know if you have really worked on CTEs & recursive CTEs, or just learned the concept, or do not know about this at all.

Let’s check this by a simple example:

DECLARE @StringToReverse VARCHAR(55)
SET @StringToReverse = 'Reverse a string with out using REVERSE() function'

;WITH cte AS (
      SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln
      UNION ALL
      SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln
      FROM cte
      WHERE ln >= 1)
SELECT @StringToReverse AS String, revStr
FROM cte
WHERE ln = 0

Output:-

Note: In the code above check line numbers 5 & 7 containing CAST function, “CAST(” AS VARCHAR(55)) AS revStr” & “CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55))”.

Applying CAST or CONVERT function is necessary here as datatype & size of both the columns in the anchor & recursive part should be same. Otherwise SQL server will throw the following error:

Msg 240, Level 16, State 1, Line 4
Types don’t match between the anchor and the recursive part in column “revStr” of recursive query “cte”.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


Advertisements

SQL Server 2012 (DENALI) TSQL – New Feature in SSMS – Surround With

August 5, 2011 Leave a comment

I just stumbled on this new exciting feature of SSMS Denali while trying my hands querying on this new environment.

Here is a scenario: You’ve  created a query or a logic with multiple lines of SQL statements. Now you want this to be validated by an IF-ELSE condition or iterate it in a WHILE loop. You will move the cursor to the top and make extra spaces for  inserting IF/WHILE {condition} BEGIN, go to the Bottom and terminate the condition/loop with END statement.

But if you are on Denali you won’t have to do this and type anything. Just a few mouse click would do for you, let’s see how:

1. Select the SQL statement or logic you want to Surround with, then right click on it, select the “Surround With…” option:

Denali SSMS - Surround With 01

2. Now double click on any 3 of your choice, here I selected the “If” condition:

Denali SSMS - Surround With 02

3. The Editor will automatically write the “IF” syntax for you:

Denali SSMS - Surround With 03

4. You just need to add the condition within brackets as per your requirement.

… nice feature introduced by Microsoft.