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”.
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.
Page Detail | URL |
---|---|
☛ SQL Advance Puzzles | https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/ |
☛ SQL Tricky Queries | https://msbiskills.com/sql-puzzles-finding-outputs/ |
☛ SQL Server Perfomance tuning Articles | https://msbiskills.com/sql-performance-tuning/ |
☛ SQL Server Articles | https://msbiskills.com/t-sql/ |
☛ SQL Interview Questions & Answers | https://msbiskills.com/sql-server-interview-questions/ |
Enjoy !!! Keep Learning
Http://MsbiSkills.com