A SQL Query to select two substrings from a known string

Question

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

Example:

Main string

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

1st substring captured

T00BE161571394 

2nd substring captured

T8LQI141529458

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

1st substring captured

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

2nd substring captured

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.

Answer 1

i guess you can use [*] to seperated. 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)