gizq gizq - 1 year ago 63
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:


Answer Source

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')

        SUBSTRING(COLUMNA, CHARINDEX(':', ColumnA) + 2, 
                   CASE WHEN CHARINDEX('/', ColumnA) = 0 THEN LEN(ColumnA)
                        ELSE CHARINDEX('/', ColumnA) END - 
                                  (CHARINDEX(':', ColumnA) + 2)) [MySubString]
FROM @MyTable 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download