CODEWITHSUNDEEP
sqlsql-server
obezejoe
obezejoe

Reputation: 43

How do I select a substring from two different patindex?

I have many different types of string, but they all follow the two same patterns:

ABC123-S-XYZ789
ABC123-P-XYZ789

QUESTION 1:

I know how I can extract the first part: ABC123
But how do I extract the second part??? XYZ789

QUESTION 2:

I can't tell beforehand if the string follows the -S- pattern or the -P- pattern, it can be different each time. Anyone who know how I can solve this?

Thanks! / Sophie

Upvotes: 4

Views: 129

Answers (4)

EzLo
EzLo

Reputation: 14189

Is this what you need?

DECLARE @Input VARCHAR(100) = 'ABC123-S-XYZ789'

SELECT
    FirstPart = SUBSTRING(
        @Input,
        1,
        CHARINDEX('-', @Input) - 1),

    SecondPart = SUBSTRING(
        @Input,
        LEN(@Input) - CHARINDEX('-', REVERSE(@Input)) + 2,
        100),

    Pattern = CASE 
        WHEN @Input LIKE '%-S-%' THEN 'S'
        WHEN @Input LIKE '%-P-%' THEN 'P' END

Upvotes: 1

NotAnAuthor
NotAnAuthor

Reputation: 1281

If you're using SQL Server 2016 or newer, you can use STRING_SPLIT

CREATE TABLE #temp (string VARCHAR(100));

INSERT #temp VALUES ('ABC123-S-XYZ789'),('ABC123-P-XYZ789');

SELECT *, ROW_NUMBER() OVER (PARTITION BY string ORDER BY string) 
    FROM #temp t
    CROSS APPLY STRING_SPLIT(t.string, '-');

I can't tell beforehand if the string folllows the -S- pattern or the -P- pattern

You can then use a CTE to get a specific part of the string:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY string ORDER BY string) rn
        FROM #temp t
        CROSS APPLY STRING_SPLIT(t.string, '-')
    )
    SELECT * FROM cte WHERE rn = 2

Upvotes: 0

Aura
Aura

Reputation: 1307

You can try following code:

SELECT CASE WHEN @a LIKE '%-S-%' THEN right(@a, CHARINDEX('-S-', @a)-1)
WHEN @a LIKE '%-P-%' THEN right(@a, CHARINDEX('-P-', @a)-1) 
ELSE NULL END AS 'ColName'
FROM tablename

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use parsename() if the string has always this kind of parts such as ABC123-S-XYZ789

select col, parsename(replace(col, '-', '.'), 1)

However, the parsename() requires the SQL Server+12 if not then you can use reverse()

select col, reverse(left(reverse(col), charindex('-', reverse(col))-1))

Upvotes: 0

Related Questions