Reeya Oberoi Reeya Oberoi - 7 months ago 9
SQL Question

How to use substring conditionally before and after two different symbols in SQL SERVER

I have a table A with ID col. Here is sample data -

ID
NT-QR-1499-1(2015)
NT-XYZ-1503-1
NT-RET-546-1(2014)


I need to select everything after first '-' from left and before '(' from the right. However, some records do not have '(', in which case, the second condition would not apply.

Here is what I need -

QR-1499-1
XYZ-1503-1
RET-546-1

Answer

You could get it done in a CASE statement, although I'd definitely take any advice from Aaron;

CREATE TABLE #TestData (ID nvarchar(50))

INSERT INTO #TestData (ID)
VALUES
('NT-QR-1499-1(2015)')
,('NT-XYZ-1503-1')
,('NT-RET-546-1(2014)')

SELECT 
ID
,CASE
    WHEN CHARINDEX('(',ID) = 0
        THEN RIGHT(ID, LEN(ID)-CHARINDEX('-',ID))
    ELSE LEFT(RIGHT(ID, LEN(ID)-CHARINDEX('-',ID)),CHARINDEX('(',RIGHT(ID, LEN(ID)-CHARINDEX('-',ID)))-1)
END Result
FROM #TestData
Comments