Search code examples
sqlsubstringcharindex

SQL substring charindex

I have a SQL field with a string like this

27/08/2018 to 31/08/2018 - GCH/10561/201312361J/HO
04/09/2018 to 07/09/2018 - GCH/2836/202788279H/WAL

This string has a reference code I need to extract. The one bolded above.

The problem is the string before it has not always the same length. See second example above.

How can I extract that code each time?

Ive tried this, but i need to the left of the last "/".....

SELECT Right('27/08/2018 to 31/08/2018 - GCH/10561/201312361J/HO', CHARINDEX('/','27/08/2018 to 31/08/2018 - GCH/10561/201312361J/HO')-1)

I think I need to use a substring and charindex but cannot get it working.

Thanks in advance for your help.

Solution

  • You can try below

    DEMO

    declare @text varchar(64)
set @text='27/08/2018 to 31/08/2018 - GCH/10561/201312361J/HO'
select reverse(substring(reverse(right(@text,CHARINDEX('-',@text)-3)),CHARINDEX('/',reverse(right(@text,CHARINDEX('-',@text)-3)))+1,10))

    OUTPUT:

    val
201312361J