Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | REVERSE a String Without Using Reverse Function / Recursive CTE / While Loop

In the puzzle we have an input string and you have find the reverse of the input string without using while loop, recursive cte and T-SQL reverse function.

Please check out the sample input and the expected output below-

Sample Input

InputStr
noitcnuf )(ESREVER gnisu tuo htiw gnirts a esreveR

Expected Output

InputStr OutputStr
Reverse a string with out using REVERSE() function noitcnuf )(ESREVER gnisu tuo htiw gnirts a esreveR

Solution 1 | Using Numbers Table


--


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

IF OBJECT_ID('tempdb..#x') IS NOT NULL
    DROP TABLE #x

SELECT NUMBER, SUBSTRING(@StringToReverse,Number,1) Chr, ROW_NUMBER() OVER (ORDER BY Number DESC) r INTO #x FROM 
( 

	SELECT 
		  DISTINCT NUMBER 
	FROM 
		  MASTER..SPT_VALUES
	WHERE
		  NUMBER > 0 AND NUMBER <= DATALENGTH(@StringToReverse) 

)h
ORDER BY NUMBER DESC

SELECT @Output=@Output+Chr FROM #x

SELECT @StringToReverse InputStr , @Output OutputStr

IF OBJECT_ID('tempdb..#x') IS NOT NULL
    DROP TABLE #x


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements