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

 OutputChrs s d g f h s d g f h s @ 1 2 1 3 1 3 1 3 1

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE” or While Loop.
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