Fetch string from dashed string in SQL Server

Question

I have a table which contains dashed strings.

I want string between last dash and second last dash

Dash means -

SQL Server table

Create table tbl1 (vdocno varchar(200))

insert into tbl1 values('Abcd-67-Hji-kok-74-Kio')
insert into tbl1 values('Bki-6kdi7-jhfi-Hfjdji-koikk-7ji4-Kloio')

Desired output values:

74
7ji4

SQLFIDDLE

There are lots of ways to accomplish this. You can use a string splitter, you can use reverse and substring. What have you tried? — Sean Lange, Sep 03 '14 at 13:33
since there don't seem to regexps in sql server, it might prove to be a challenge to write in plain sql http://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function — MarianP, Sep 03 '14 at 13:43
@MarianP I am not the one who downvoted the question. I close-voted the question for being unclear. — Thorsten Dittmar, Sep 03 '14 at 14:03
Here's a [thread on splitting out a string][1]. [1]: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x/25391776#25391776 — Katherine Elizabeth Lightsey, Sep 03 '14 at 14:06

Tanner · Accepted Answer · 2014-09-03T14:16:36.607

It's not the prettiest and I have no doubt it could be done more efficiently, but here's something that works that you could improve upon if required:

Demo SQL Fiddle

create table tbl1 (vdocno varchar(200))

insert into tbl1 values('Abcd-67-Hji-kok-74-Kio')
insert into tbl1 values('Bki-6kdi7-jhfi-Hfjdji-koikk-7ji4-Kloio')

select vdocno,
reverse(substring(
    reverse(substring(vdocno, 1, LEN(vdocno) - CHARINDEX('-',REVERSE(vdocno)))), 
         1, CHARINDEX('-',
                        reverse(substring(vdocno, 1, LEN(vdocno) - CHARINDEX('-',REVERSE(vdocno))))
                     ) 
         - 1))
from tbl1

This reverses the string and strips off the section before and including the last dash, then using the reserved string, it removes the content after the first dash, before reversing it to be the right way round again.

Fetch string from dashed string in SQL Server

1 Answers1

Demo SQL Fiddle