在t-sql中找到匹配模式后的子字符串

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

这可能真的很容易，但T-SQL远非我的强项。

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:

我想从这里提取007941230X。此子字符串的长度会有所不同，但格式将始终为:

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

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

有人知道如何在t-sql中获取j值的子字符串吗?

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

我试着用patindex，但是找不出来。

2 个解决方案

#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

#2

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

如果弦总是从第8位开始，然后长度变化，你可以这样做:

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:

如果您不知道它从第8个字符开始，可以通过计算值来实现。下面是一个带有子查询的示例:

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.

T-SQL中的字符串函数不像其他语言那样强大。但有时有必要把这样的解决方案拼凑在一起。

#1