Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Separation (Chr & Int) Puzzle

In this puzzle you have to separate one column into 5 columns. The input column is alpha & numeric characters. The sequence of the characters should not change. E.g. If you get a char at the start then all the continuous chars will go into the 1st column.

Please check out the sample input values and sample expected output below.

Can you do that in simple query ?. Functions and recursions are not allowed.

Sample Input

Id Data
1 28465dh268ro2785
2 2fjlggh2fsdfk348
3 23fjfd358506vmj6

Expected Output

Id Data 1 2 3 4 5
1 28465dh268ro2785 28465 dh 268 ro 2785
2 2fjlggh2fsdfk348 2 fjlggh 2 fsdfk 348
3 23fjfd358506vmj6 23 fjfd 358506 vmj 6

Script

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

--

CREATE TABLE Split5Data
(
	 Id INT IDENTITY(1,1) PRIMARY KEY
	,Data VARCHAR(1000)
)
GO

INSERT INTO Split5Data VALUES 
('28465dh268ro2785'),
('2fjlggh2fsdfk348'),
('23fjfd358506vmj6')
GO

--

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


--
 
;WITH CTE AS
(
	SELECT Id,Data, SUBSTRING( Data ,  MIN(Number) , MAX(Number) - MIN(Number) + 1 ) Chr 
		,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY MIN(Number)) rnk 
	FROM
	(
		SELECT * , SUM(cols) OVER (PARTITION BY Id ORDER BY Number) grouper FROM 
		(
			SELECT * ,  CASE WHEN c = lag(c) 
						over(PARTITION BY ID order by Number) THEN 0 ELSE 1 END cols   FROM 
			(
				SELECT * , SUBSTRING(Data,Number,1) rt , CASE WHEN TRY_CAST(SUBSTRING(Data,Number,1) AS INT) IS NULL THEN 1 ELSE 0 END c FROM Split5Data
				CROSS APPLY
				(
					SELECT DISTINCT number FROM master..spt_values
					WHERE number > 0 and number <= LEN(Data)
				)r			
			)w
		)e
	)r
	GROUP BY Id,grouper,Data
)
SELECT Id,Data,[1],[2],[3],[4],[5]
FROM CTE
PIVOT (MAX(Chr) FOR rnk IN ([1],[2],[3],[4],[5])) p
ORDER BY Id

--

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