我需要一个 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]%'更多推荐
从已知字符串中选择两个子字符串的 SQL 查询
发布评论