SQL Puzzle | Convert a string into a table without any delimiter

Puzzle Statement

You have an input string and you have create a table from it. Each character will be converted in a row with a single column. Check out sample input and expected output.

Sample Input

STRING = ‘sdgfhsdgfhs@121313131’

Expected output

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE” or While Loop.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script

Use the below script to generate the source table and fill them up with the sample data.

Solutions # – A loop should be easy in this case. I am here looking for solutions where we don’t have to use a loop. Still I have given solution with while loop.

SOLUTION 1 | WHILE LOOP

--

DECLARE @Counter AS INT = 1
DECLARE @Chrs TABLE (OutputChrs Char(1))

WHILE (@Counter <= DATALENGTH(@str)) 
BEGIN
    INSERT INTO @Chrs VALUES
    (    
        SUBSTRING(@str,@Counter,1)  
    ) 
	SET @Counter = @Counter + 1 
END
SELECT OutputChrs FROM @Chrs

--

SOLUTION 2 | RECURSIVE CTE

--

DECLARE @str AS VARCHAR(100) = 'sdgfhsdgfhs@121313131'

;WITH CTE AS
(
      SELECT  1 start , SUBSTRING(@str,1,1) OutputChrs
      UNION ALL
      SELECT  start + 1 start , SUBSTRING(@str , start+1 , 1) OutputChrs
      FROM CTE WHERE start < DATALENGTH(@str)
)
SELECT OutputChrs FROM CTE

--

SOLUTION 3 | USING SELECT

--

DECLARE @str AS VARCHAR(100) = 'sdgfhsdgfhs@121313131'

SELECT SUBSTRING(@str,number,1) OutputChrs FROM ( 
SELECT DISTINCT number FROM 
MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@str) ) 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