Get substring from varchar column in SQL server table

Question

I have one column named Name, and in the column I have values

Name 001 BASI Distributor (EXAM) 002 BASI Supplier (EXAM2) MASI DISTRIBUTOR (EXAM002) MASI SUPPLIER (EXAM003) EXAM_ND Distributor Success System Test (EXAM_ND) EXAM_SS Supplier Success System Test (EXAM_SS) 

Now I want to separate the value inside () from this entire string. How do I get this, I tried for SUBSTRING (Name ,22 ,4 ) , but that will help for one thing. I want to get the result using some unique solution.

Answer 1

Try this option -

 DECLARE @temp TABLE (st NVARCHAR(50)) INSERT INTO @temp (st) VALUES ('001 BASI Distributor (EXAM)'), ('002 BASI Supplier (EXAM2)'), ('MASI DISTRIBUTOR (EXAM002)'), ('MASI SUPPLIER (EXAM003)'), ('EXAM_ND Distributor Success System Test (EXAM_ND)'), ('EXAM_SS Supplier Success System Test (EXAM_SS)') SELECT SUBSTRING( st, CHARINDEX('(', st) + 1, CHARINDEX(')', st) - CHARINDEX('(', st) - 1 ) FROM @temp 

Output -

 ------------- EXAM EXAM2 EXAM002 EXAM003 EXAM_ND EXAM_SS 

Answer 2

 SELECT SUBSTRING(Name, CHARINDEX('(', Name) + 1, CHARINDEX(')', Name) - CHARINDEX('(', Name) - 1) 

Answer 3

Another option:

 SELECT *, REPLACE(SUBSTRING(Name, CHARINDEX('(', Name) + 1, LEN(Name)), ')', '') FROM dbo.test136 

This frees up one call to CHARINDEX (search for a closing ) ) on the assumption that your Name values ​​will never contain anything after the part enclosed in brackets. If they do, use other suggestions.

Answer 4

Try:

 SELECT REPLACE(SUBSTRING(Name, PATINDEX('%(%', Name)+1, LEN(Name)), ')', '') FROM YourTable