SQL Puzzle | Convert a string into a table n number of times ( n = length of the string )

Puzzle Statement

One of my friend today asked me a puzzle. The puzzle says you have an input string and you have to insert that string into a table without using a loop. One more condition is there, you have to insert that string number of times the length of that string. E.g. Let’s say that the string is ‘Pawan’ So you have insert Pawan 5 times in a table. 5 is the length(Pawan).

Sample Input

Expected output

Rules/Restrictions

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

--

CREATE TABLE PrintLengthTimes
(
	Value VARCHAR(100)
)
GO

INSERT INTO PrintLengthTimes Values ('ISHA')
INSERT INTO PrintLengthTimes Values ('PAWAN')

--

SOLUTION 1 | USING XML & CROSS APPLY

--

SELECT SplittedString FROM
(
    SELECT  CAST(REPLICATE(CONCAT('<A>' , Value , '</A>' ),DATALENGTH(Value)) AS XML) Xmlcol FROM PrintLengthTimes
) s
CROSS apply
(
    SELECT ProjectData.D.value('.', 'VARCHAR(100)') as SplittedString
    FROM s.xmlcol.nodes('A') as ProjectData(D)
)a

--

SOLUTION 2 | USING Numbers TABLE & CROSS APPLY

--

SELECT Value OutputChrs FROM PrintLengthTimes 
CROSS APPLY 
( 
	SELECT DISTINCT number FROM
	MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(Value) 
)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

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