SQL Puzzle | The Facebook Puzzle # 1

Puzzle Statement

This puzzle was asked in social media ( Facebook ), Here you have a string and you have to insert a character(_) underscore after every third character in the string. Please check sample input and expected output.

Sample Input String

vinod kumar reddy ”k

Expected Output

vin_od _kum_ar _red_dy _’k

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

SOLUTION 1 | Using Simple SELECT & Numbers Table

--

DECLARE @str AS VARCHAR(100) = 'vinod kumar reddy ''k'
DECLARE @outs AS VARCHAR(500) = ''

SELECT @outs = Concat(@outs,'',CASE WHEN number % 3 = 0 THEN CONCAT(SUBSTRING(@str,number,1) , '_') ELSE SUBSTRING(@str,number,1) END)
FROM 
( 
	SELECT DISTINCT number FROM
	MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@str)
)v
SELECT @outs

--

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