CODEWITHSUNDEEP
sqlsql-serversql-server-2005
gofor.net
gofor.net

Reputation: 4298

Get substring from varchar column in SQL server table

I have one column called 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 the () from this whole string.How I will get this I tried for the SUBSTRING (Name ,22 ,4 ) but this will help for single one I want to get the result using some unique solution.

Upvotes: 4

Views: 14800

Answers (4)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

One more option:

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

This spares one CHARINDEX call (searching for the closing )) on the assumption that your Name values never contain anything after the parenthesised part. If they do, use other suggestions.

Upvotes: 3

TechDo
TechDo

Reputation: 18659

Please try:

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

Upvotes: -1

Devart
Devart

Reputation: 122042

Try this one -

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

Upvotes: 3

Biju P
Biju P

Reputation: 108

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

Upvotes: 5

Related Questions