SQL Puzzle | Separate Out Integer and Character Data from a column

In this SQL Puzzle, you have a table with a Varchar column. This column contains integer and character values. We need to write the logic to separate out integer and character data into 2 different columns.

So effective we will have 3 columns in the output i.e. Original Data, Integer Data, Character Data.

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

Sample Input

Value
123
ABC
dskfsda1224

Expected Output

Value IntValues ChrValues
123 123
ABC ABC
dskfsda1224 1224 dskfsda

Script

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

 ```-- CREATE TABLE SeperateNumbersAndCharcters ( Value VARCHAR(100) ) GO INSERT INTO SeperateNumbersAndCharcters VALUES ('dskfsda1224'), ('123'), ('ABC'), ('23874283bsdjfhsjadkhfas09') GO CREATE NONCLUSTERED INDEX Ix_Value ON SeperateNumbersAndCharcters ( Value ) -- ```

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 Numbers Table – Set Based Approach

 ``` -- SELECT Value , ISNULL(IntValues,'') IntValues , ISNULL(ChrValues,'') ChrValues FROM SeperateNumbersAndCharcters x CROSS APPLY ( SELECT DISTINCT ( SELECT '' + y.chr FROM ( SELECT Number, CASE WHEN ASCII(SUBSTRING(Value,Number,1)) BETWEEN 48 AND 57 THEN 1 ELSE 2 END v , SUBSTRING(Value,Number,1) chr FROM ( SELECT DISTINCT Number FROM MASTER..SPT_VALUES WHERE NUMBER > 0 AND NUMBER <= DATALENGTH(Value) )v) y WHERE ( 1 = y.v ) FOR XML PATH('') ) AS IntValues ,( SELECT '' + y.chr FROM (SELECT Number, CASE WHEN ASCII(SUBSTRING(Value,Number,1)) BETWEEN 48 AND 57 THEN 1 ELSE 2 END v ,SUBSTRING(Value,Number,1) chr FROM ( SELECT DISTINCT Number FROM MASTER..SPT_VALUES WHERE NUMBER > 0 AND NUMBER <= DATALENGTH(Value) )v) y WHERE ( 1 <> y.v ) FOR XML PATH('') ) AS ChrValues FROM (SELECT Number, CASE WHEN ASCII(SUBSTRING(Value,Number,1)) BETWEEN 48 AND 57 THEN 1 ELSE 2 END v , SUBSTRING(Value,Number,1) chr FROM ( SELECT DISTINCT Number FROM MASTER..SPT_VALUES WHERE NUMBER > 0 AND NUMBER <= DATALENGTH(Value) )v) x )r -- ```

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