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

Id Datas
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

Expected Output

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

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

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.

Page Detail URL