SQL Query to select two substrings from a known string
I need a SQL query to get two charstrings from one main string, the return values start with T#######@@###@@####
. The length of the main line changes.
Example:
Main line
@code = 025121710TestPASS * 68242850AD * 68242382AF * 1UJ97DX9AF * 68248793AB * 68236772AB * 56054275AG * NoPN * 1UW38DX9ACNoPNT00BE161571394 * T8LQI1415NoP * NoPNoPN * NoPN
1st substring captured
T00BE161571394
Second substring captured
T8LQI141529458
I've come up with this so far, to no avail:
1st substring captured
SELECT left(RIGHT(code, 51), 15)
Second substring captured
SELECT left(RIGHT(code, 35), 15)
Can anyone help me? I'm not sure how to take into account the length correctly and separate the substrings in the correct order.
source to share
Try this, the full list of substrings in the main string will be selected here
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]%'
source to share
I think you can use [*] for separation. reverse string and split
declare @string1 nvarchar(250)
declare @string2 nvarchar(250)
declare @myString nvarchar(500)= '025121710TestPASS*68242850AD*68242382AF*1UJ97DX9AF*68248793AB*68236772AB*56054275AG*NoPN*1UW38DX9ACNoPNT00BE161571394 *T8LQI141529458*NoPNNoPNNoPN*NoPN'
set @myString = REVERSE(@myString)
DECLARE @StartPos int, @Length int
select @StartPos = CHARINDEX('*', @myString)
set @myString = SUBSTRING(@myString,(@StartPos+1),len(@myString)-@StartPos)
select @StartPos = CHARINDEX('*', @myString)
set @myString = SUBSTRING(@myString,(@StartPos+1),len(@myString)-@StartPos)
select @StartPos = CHARINDEX('*', @myString)
select @string1 = SUBSTRING(@myString,0,@StartPos)
set @myString = SUBSTRING(@myString,(@StartPos+1),len(@myString)-@StartPos)
select @StartPos = CHARINDEX('*', @myString)
select @string2 = SUBSTRING(@myString,0,16)
select REVERSE(@string1) ,REVERSE(@string2)