SQL Puzzle | The Two Underscore Puzzle | Improving my SQL BI Skills

Tags

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

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

5 thoughts on “SQL Puzzle | The Two Underscore Puzzle”

sanjay singh said:

May 2, 2018 at 12:31 PM

;with cte
as
(
select ID,cast (‘‘ + REPLACE(data,’_’,’‘)+’‘ as xml) as aa
from #TwoUnderscores
)
,cte1
as
(
select id,p.value( ‘.’ , ‘varchar(10)’) as dd,ROW_NUMBER() over (Partition by id order by(select null)) as rnk
from cte c
cross apply aa.nodes(‘A’) U(P)
)
select id,dd from cte1
where rnk=2

LikeLike

sanjay singh said:

May 2, 2018 at 12:33 PM

;with cte
as
(
select ID,cast (” + REPLACE(data,’_’,”)+” as xml) as aa
from #TwoUnderscores
)
,cte1
as
(
select id,p.value( ‘.’ , ‘varchar(10)’) as dd,ROW_NUMBER() over (Partition by id order by(select null)) as rnk
from cte c
cross apply aa.nodes(‘x’) U(P)
)
select id,dd from cte1
where rnk=2

LikeLike

Gregor Kralj said:

May 27, 2019 at 2:29 PM

with cte as
(select *,
CHARINDEX(‘_’,Data) firstone,
CHARINDEX(‘_’, REPLACE(Data, LEFT(Data,CHARINDEX(‘_’,Data)),”)) as secondone
from TwoUnderscores
)
select SUBSTRING(Data,firstone+1,secondone-1)
from cte

LikeLiked by 1 person

Nikhil Chavan said:

June 1, 2020 at 7:25 PM

select
Substring(Substring(Data,1,Charindex(‘_’,Data,Charindex(‘_’,Data)+1)-1),
(Charindex(‘_’,Substring(Data,1,Charindex(‘_’,Data,Charindex(‘_’,Data)+1)-1))+1),
len(Substring(Data,1,Charindex(‘_’,Data,Charindex(‘_’,Data)+1)-1)))
from #TwoUnderscores

LikeLike

- Pawan Kumar Khowal said:
  
  July 6, 2020 at 7:21 AM
  
  Hi,
  
  Thank you for the comment.
  
  Pawan
  
  LikeLike

Share this

Related

5 thoughts on “SQL Puzzle | The Two Underscore Puzzle”

Leave a comment Cancel reply