Tags

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


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
23874283bsdjfhsjadkhfas09
ABC
dskfsda1224

Expected Output

Value IntValues ChrValues
123 123
23874283bsdjfhsjadkhfas09 2387428309 bsdjfhsjadkhfas
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