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

Expected Output

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

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 
        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 -]

Ref – https://msbiskills.com/2016/06/03/sql-puzzle-the-separation-puzzle/

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