SQL PUZZLE | Extract data Between Hyphens without using SubString function? | SQL Interview Question

In the puzzle you have to extract data between the two hyphens without using SUBSTRING function. For more details please refer sample input and expected output.

Sample Input

Id Vals
1 HelloPawan-25-Hi
4 Hello-22-where are you
5 Hunn-98-I am here
2
3 NULL

Expected output

Id Vals
1 25
4 22
5 98
2
3 NULL

Script – DDL and INSERT sample data

 ```-- CREATE TABLE ExtractData ( Id INT , Vals VARCHAR(100) ) GO INSERT INTO ExtractData VALUES (1,'HelloPawan-25-Hi' ), (4,'Hello-22-where are you' ), (5,'Hunn-98-I am here' ), (2,''), (3,NULL) GO SELECT * FROM ExtractData GO -- ```

SOLUTION | Using ParseName

 ```-- SELECT Id,IIF(Vals='','',PARSENAME(REPLACE(vals,'-','.'),2)) Vals FROM ExtractData -- ```

OUTPUT

 ```-- Id Vals ----------- ---------- 1 25 4 22 5 98 2 3 NULL (5 rows affected) -- ```

SOLUTION | Using split

 ```-- SELECT Id,ISNULL(Value,Vals) Vals FROM ( SELECT Id,Vals,Value,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY (SELECT NULL)) rnk FROM ( SELECT *, IIF ( vals '', CAST(''+ REPLACE(Vals,'-','')+ '' AS XML) , '') po FROM ExtractData )rt OUTER APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o )x WHERE rnk = 2 OR Value IS NULL -- ```

OUTPUT

 ```-- Id Vals ----------- ---------- 1 25 4 22 5 98 2 3 NULL (5 rows affected) -- ```

Enjoy 🙂

Pawan Khowal

Pawan is a SQL Server Developer. 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 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/