SQL Puzzle | Parse Complex String Data Puzzle

In this puzzle you have to parse Datas Column and get numeric data before + in one column and numeric data after + in another column. For more details please check the sample input and expected output.

Sample Input

Expected Output

Script

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

--

CREATE TABLE MixedValues
(
	 Id INT
	,Datas VARCHAR(1000)
)
GO

INSERT INTO MixedValues VALUES
(1,'p 9.870 + 5.678 z'),
(2,'q 1.008+1.989 q'),
(3,'g 2.8+ 4.5 r'),
(4,'ty 2.7 + 5.6 br'),
(5,'pwa 4+6 qa'),
(6,'Now 9 + 8 wer')
GO

SELECT * FROM MixedValues
GO

--

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

--

SELECT Id, Datas
	,TRIM(SUBSTRING(a,PATINDEX('%[0-9]%',a),1000)) a 
	,SUBSTRING(b,0,PATINDEX('%[a-zA-Z]%',b)-1)b 
FROM 
(
	SELECT * 
		,TRIM(LEFT( Datas, CHARINDEX('+',Datas) - 1 )) a
		,TRIM(SUBSTRING( Datas , CHARINDEX('+',Datas) + 1 , 1000 )) b
	FROM MixedValues
)b

--

Output-1

--                            

Id          Datas                   a             b
----------- ----------------------- ------------- ----------------
1           p 9.870 + 5.678 z       9.870         5.678
2           q 1.008+1.989 q         1.008         1.989
3           g 2.8+ 4.5 r            2.8           4.5
4           ty 2.7 + 5.6 br         2.7           5.6
5           pwa 4+6 qa              4             6
6           Now 9 + 8 wer           9             8

(6 rows affected)

                                    
--

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Author Introduction: Pawan Khowal

Pawan is a SQL Server Expert. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Enjoy !!! Keep Learning

Http://MsbiSkills.com