Find a substring after a matched pattern in t-sql

Question

This may be really easy but T-SQL is far from my forte.

I have a bunch of really long strings that contain a segment that looks like this:

   ~GS^PO^007941230X^107996118^20130514^

I'd like to extract 007941230X out of this. The length of this substring will vary but the format will always be:

  ~xxxx^.....^xxxxx^~GS^PO^jjjjjjjj^xxx^xxxx^....~

Does anyone know how to get the substring of the values for j in t-sql?

I was trying to use patindex somehow but can't figure it out.

Solution 1

Here's a working example

declare @var varchar(1000) = '~xxxx^.....^xxxxx^~GS^PO^jjjjjjjj^xxx^xxxx^....'
declare @start_position int, @end_position int
declare @temp_string varchar(100)
select @start_position = PATINDEX('%GS^PO^%', @var)
print @start_position
select @temp_string = SUBSTRING(@var, @start_position + 6, 10000)
print @temp_string
select @end_position = PATINDEX('%^%', @temp_string)
print @end_position
print substring(@temp_string, 1, @end_position -1)

20
jjjjjjjj^xxx^xxxx^....
9
jjjjjjjj

Solution 2

If the string always starts at the 8th position and then varies in length, you can do:

with t as (
      select '~GS^PO^007941230X^107996118^20130514^' as val
     )
select substring(val, 8, 
                 charindex('^', substring(val, 8, len(val)))-1
                )
from t;

If you don't know that it begins at the 8th character, you can do it by calculating the value. Here is an example with a subquery:

with t as (
      select '~GS^PO^007941230X^107996118^20130514^' as val
     )
select substring(val, start, 
                 charindex('^', substring(val, start, len(val)))-1
                ), start
from (select charindex('^', t.val, 
                       charindex('^', t.val) +1
                      ) + 1 as start, t.*
      from t
     ) t

Strings functions in T-SQL are not as powerful as in other languages. But sometimes it is necessary to piece together solutions like this.

Solution 3

Get campid from given URL:

declare @LinkUrl nvarchar(max)='http://example.com/campid=4546&custid=Objets',
            @startPosition int,
            @endPosition int,
            @tempString nvarchar(max)
    
     select @startPosition = PATINDEX('%campid=%', @LinkUrl) 
     select @tempString = SUBSTRING(@LinkUrl, @startPosition + 7, 10000) 
     select @endPosition = PATINDEX('%&custid%', @tempString)
     select distinct substring(@tempString, 1, @endPosition -1)
    

Output: 4546