get values from 2nd occurncy in sql server

Question

Hi I have one doubt in sql server .
how to get values from 2nd occurance from _ symbol in sql server.

Table:Product_details

CREATE TABLE [dbo].[product_details](
[name] varchar NULL
) ON [PRIMARY]
GO
INSERT [dbo].[product_details] ([name]) VALUES (N'abc_xyz_pen')
INSERT [dbo].[product_details] ([name]) VALUES (N'def_rav_pen_two')
INSERT [dbo].[product_details] ([name]) VALUES (N'ade_rav_ted_ted_pen')
INSERT [dbo].[product_details] ([name]) VALUES (N'te_ty_te_de_rd_te')

based on above table i want output like below

Name
pen
pen_two
ted_ted_pen
te_de_rd_te

I tried like below
select substring ( name,charindex('_',name,1),len(name)) from product_details

above query not giving exact result .could you please help on it
how to write a query in sql server .

Answer 1

Hi @tani oju ,

Welcome to the Microsoft SQL Server Q&A Forum！

Please refer to:

select right([Name],len([Name])-charindex('_',[Name],charindex('_',[Name])+1)) [name]   
from [dbo].[product_details]  

Output:

name  
pen  
pen_two  
ted_ted_pen  
te_de_rd_te  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.