SQL Puzzle | Get First Character After all occurrences of Underscore from the string

In this puzzle you have to get first character after underscore from the string. The catch is that you have to fetch all the first character from all the occurrences of underscore. For more details please check the sample input and expected output.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

DECLARE @ AS VARCHAR(1000) = '_a_b_c_d_'
SELECT @

--

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 | for SQL SERVER 2012-

--

DECLARE @x AS XML=''
DECLARE @ AS VARCHAR(1000) = '_a_b_c_d_'
SET @x = CAST('<A>'+ REPLACE(@,'_','</A><A>')+ '</A>' AS XML)
;WITH CTE AS
(
	SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)      
)
,CTE1 AS 
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM CTE
)
,CTE2 AS
(
	SELECT SUBSTRING(Value,1,1) v , rnk FROM CTE1
)
SELECT CASE WHEN LEFT(@,1) <> '_' THEN MAX(SUBSTRING(u,2,LEN(u))) ELSE MAX(u) END finalstr from (
SELECT  
		(
			SELECT '' + v  
			FROM CTE2 a               
			FOR XML PATH('') 
		)u
FROM CTE2 )x
               


--

Output-1

--                            

finalstr
----------------
abcd

(1 row affected)
                                    
--

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