gizq gizq - 1 month ago 6
SQL Question

Substring between 2 character or full length SQL

I have a question, let's say I have a column A with values:

te SN: 123454A / Lo
te SN: 12348887
te SN: 547896


I want to retrieve everything that is +2 after
:
up to the
/
but in case
/
doesn't exist, get everything after
:
.

So my result would be:

123454A
12348887
547896

Answer

This will find the colon and then add two. Find the slash if it exists, if it doesn't it will use the end of the string.

DECLARE @MyTable TABLE ( ColumnA VARCHAR(50) )
INSERT @MyTable (ColumnA) VALUES ('te SN: 123454A / Lo'), 
                                 ('te SN: 12348887'), 
                                 ('te SN: 547896')

SELECT  *, 
        SUBSTRING(COLUMNA, CHARINDEX(':', ColumnA) + 2, 
                   CASE WHEN CHARINDEX('/', ColumnA) = 0 THEN LEN(ColumnA)
                        ELSE CHARINDEX('/', ColumnA) END - 
                                  (CHARINDEX(':', ColumnA) + 2)) [MySubString]
FROM @MyTable 
Comments