CODEWITHSUNDEEP
sqlsql-server
Reeya Oberoi
Reeya Oberoi

Reputation: 861

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

Upvotes: 2

Views: 79

Answers (5)

Jay
Jay

Reputation: 49

SELECT CASE WHEN CHARINDEX('(',ID) > 0 THEN SUBSTRING(ID,CHARINDEX('-',ID)+1,(CHARINDEX('(',ID)-CHARINDEX('-',ID)-1)) ELSE SUBSTRING(ID,CHARINDEX('-',ID)+1) END AS New_Column_Name FROM Table_Name

First it will check whether "(" present or not . If present then it will fetch the data from next position of "-" to before the position of "(". otherwise it will fetch the data from next position of "-" to till end.

Upvotes: 1

Edu
Edu

Reputation: 2643

DECLARE @str varchar(64);
DECLARE @start int;
DECLARE @length int;
SELECT @str = 'NT-QR-1499-1(2015)';
/*SELECT @str = 'NT-XYZ-1503-1';*/

SELECT @start = CHARINDEX('-', @str) + 1;
SELECT @length = CHARINDEX('(', @str) - @start;
IF (@length > 0)
    SELECT SUBSTRING(@str, @start, @length)
ELSE
    SELECT SUBSTRING(@str, @start, LEN(@str))
GO

Upvotes: 1

Tom H
Tom H

Reputation: 47444

It looks like your column is not actually a single data element, but multiple data elements that have been concatenated together. A bad idea for database design, which is causing the problem that you're having now.

This should give you what you need, but strongly consider separating the column into the required pieces.

SELECT
    SUBSTRING(id, CHARINDEX('-', id) + 1, LEN(id) - CHARINDEX('(', REVERSE(id)) - CHARINDEX('-', id))
FROM
    My_Table

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT y.i, SUBSTRING(ID, x.i + 1, IIF(y.i = 0, LEN(ID), y.i - x.i - 1))
FROM mytable
CROSS APPLY (SELECT CHARINDEX('-', ID)) AS x(i)
CROSS APPLY (SELECT CHARINDEX('(', ID)) AS y(i)

Upvotes: 1

Rich Benner
Rich Benner

Reputation: 8113

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

Upvotes: 2

Related Questions