SQL Puzzle | The Separation Puzzle – 1 [ 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
Pawan111000024
PawanSh0000111
PawanSh0000111
RameshSh00001111111

Expected Output

Ints chars
Pawan 111000024
PawanSh 0000111
PawanSh 0000111
RameshSh 00001111111

Script

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

 -- CREATE TABLE SeperateNumbersAndCharcters ( Value VARCHAR(100) ) GO INSERT INTO SeperateNumbersAndCharcters VALUES ('Pawan111000024'), ('PawanSh0000111'), ('PawanSh0000111'), ('RameshSh00001111111') GO CREATE NONCLUSTERED INDEX Ix_Value ON SeperateNumbersAndCharcters ( Value ) --

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution 1 , Using Numbers Table – Set Based Approach

 -- SELECT SUBSTRING(Value, 0, PATINDEX('%[0-9]%', Value)) Ints, SUBSTRING(Value, PATINDEX('%[0-9]%', Value), LEN(Value)) chars FROM SeperateNumbersAndCharcters --

[Note the above will not if you have number and characters mixed with each other . For Example – 23874283bsdjfhsjadkhfas09 , For that please refer below -]

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