从已知字符串中选择两个子字符串的 SQL 查询

本文介绍了从已知字符串中选择两个子字符串的 SQL 查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要一个 SQL 查询来从一个主字符串中获取两个字符字符串，返回的值以 T#######@@###@@#### 开头.主字符串长度变化.

I need a SQL query to get two charstrings from one main string, the returned values start with T#######@@###@@####. The main string length changes.

示例:

主字符串

@code=025121710TestPASS*68242850AD*68242382AF*1UJ97DX9AF*68248793AB*68236772AB*56054275AG*NoPN*1UW38DX9ACNo0PN5PN5PN5PN4No0PN5PN15PN4No0PN5PN194No0PN5PN194No0PN5PN194No0PN5PN134>

@code=025121710TestPASS*68242850AD*68242382AF*1UJ97DX9AF*68248793AB*68236772AB*56054275AG*NoPN*1UW38DX9ACNoPNT00BE161571394 *T8LQI141529458*NoPNNoPNNoPN*NoPN

捕获的第一个子串

T00BE161571394

捕获的第二个子串

T8LQI141529458

到目前为止我已经想出了这个但无济于事:

I've come up with this so far but to no avail:

捕获的第一个子串

SELECT left(RIGHT(code, 51), 15)

捕获的第二个子串

SELECT left(RIGHT(code, 35), 15)

有人可以帮我吗?我不确定如何正确计算长度并以正确的顺序分隔子字符串.

Can someone please help me? I am not sure how to account for the length correctly and separate the substrings in the correct order.

推荐答案

试试这个，这将选择主字符串中子字符串的完整列表

Try this on, This will select complete list of substrings in your main string

declare @myString nvarchar(500)= '025121710TestPASS*68242850AD*68242382AF*1UJ97DX9AF*68248793AB*68236772AB*56054275AG*NoPN*1UW38DX9ACNoPNT00BE161571394 *T8LQI141529458*NoPNNoPNNoPN*NoPN' ;with T(ind,pos) as ( select charindex('T', @myString), 1 union all select charindex('T', substring(@myString,ind+1,len(@myString)))+ind,pos+1 from t where pos > 0 and ind <> charindex('T', substring(@myString,ind+1,len(@myString)))+ind ) select substring(@myString,ind,14) as YourString from t where substring(@myString,ind,14) NOT LIKE '%[^a-zA-Z0-9]%'