Substring between 2 character or full length SQL

Question

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

Which server do you use? functions available are different per server. — Roger, Nov 01 '16 at 21:55
the server is very important, this because not all use the same procedural extension, so get a substring can be different of one server to other, https://en.wikipedia.org/wiki/SQL. And you need return it in a single Select without using custom scalar function? — Byron, Nov 01 '16 at 22:13
I've given a minus vote, because no real question has been asked. You should have tried something yourself and then come here with the concrete problem (e.g. "I have this query ..., but it fails, when there is a slash before the colon. How do I look for a slash only after the colon instead?"). — Thorsten Kettner, Nov 03 '16 at 14:04

score 1 · Answer 1 · answered Nov 01 '16 at 22:58

1

As you haven't tagged your DBMS, here is the algorithm. Please look up the string functions available in your DBMS yourself.

Get the substring ...

from the position of the first ': ' in your string + 2
to the position of the first following '/' in your string with an added slash - 1

And here is how to add a slash in standard SQL: col || '/'. Adding the slash guarantees that looking for a slash won't fail and we either read to a slash existent in the original string or till the end of the original string.

answered Nov 01 '16 at 22:58

Thorsten Kettner

89,309
7
49
73

SQL Server 2012 – gizq Nov 03 '16 at 13:30
Okay, but now that you have the algorithm and can look up what functions are available in your DBMS (https://msdn.microsoft.com/en-us/library/ms181984.aspx), where is your problem? Where are you stuck? Which functions did you try in vain? Or haven't you even tried? – Thorsten Kettner Nov 03 '16 at 13:59

O_Z · Answer 2 · 2016-11-03T15:21:48.223

0

I assumed you use MySql

select SUBSTRING(myColumn FROM LOCATE(':',myColumn)+2,FOR LOCATE('/',myColumn) -LOCATE(':',myColumn)-2) from myTable

If you use Mssql then

    select SUBSTRING(myColumn , CHARINDEX(':',myColumn)+2, CHARINDEX('/',myColumn) -CHARINDEX(':',myColumn)-2)      
from myTable

I can not run the MSSQL so I just translated, hope it works

edited Nov 03 '16 at 15:21

answered Nov 01 '16 at 22:08

O_Z

1,515
9
11

You missed the "up to the /" part. – jarlh Nov 01 '16 at 22:31
SQL Server 2012 – gizq Nov 03 '16 at 13:31
@gizq change the post, hope it works.Please update me – O_Z Nov 03 '16 at 15:22

score 0 · Answer 3 · answered Nov 03 '16 at 13:44

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

Substring between 2 character or full length SQL

3 Answers3