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”.

 ``` -- ;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,,,,, FROM CTE PIVOT (MAX(Chr) FOR rnk IN (,,,,)) p ORDER BY Id -- ```