从已知字符串中选择两个子字符串的 SQL 查询 [英] A SQL Query to select two substrings from a known string

问题描述

我需要一个 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 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！