SQL Puzzle | The Two Underscore Puzzle

In this puzzle you have to extract the data between first and second underscore(_). The length of the data between these two underscore will be random. Please check out the sample input values and sample expected output below.

Can you provide any other logic?

Sample Input

Id Data
1 1234567890123_ABCD_AN_XX_SRCV_A
2 1234567890123_ABCDE_AN_XX_FARB_B
3 eshfew_12_45

Expected Output

Id Data
1 ABCD
2 ABCDE
3 12

Script

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

 ``` -- CREATE TABLE TwoUnderscores ( Id INT IDENTITY(1,1) PRIMARY KEY ,Data VARCHAR(1000) ) GO INSERT INTO TwoUnderscores VALUES ('1234567890123_ABCD_AN_XX_SRCV_A'), ('1234567890123_ABCDE_AN_XX_FARB_B'), ('eshfew_12_45') GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ``` -- SELECT Id, SUBSTRING(Data, CHARINDEX('_',Data,1) + 1, CHARINDEX('_',Data,CHARINDEX('_',Data,1) + 1 ) - CHARINDEX('_',Data,1) - 1) Data FROM TwoUnderscores -- ```

Execution

 ``` -- /*------------------------ SELECT Id, SUBSTRING(Data, CHARINDEX('_',Data,1) + 1, CHARINDEX('_',Data,CHARINDEX('_',Data,1) + 1 ) - CHARINDEX('_',Data,1) - 1) Data FROM TwoUnderscores ------------------------*/ Id Data ----------- ------------------ 1 ABCD 2 ABCDE 3 12 (3 row(s) affected) -- ```

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