A SQL Query to select two substrings from a known string

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:

  • Advanced SQL Update query
  • SqlConnection SqlCommand SqlDataReader IDisposable
  • How long does Access keep unflushed transactions locally?
  • SOAP web service returning incorrect response
  • How to filter Time portion of a DateTime column
  • Show Grouped By Items in Comma Separated Format
  • 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.

    2 Solutions collect form web for “A SQL Query to select two substrings from a known string”

    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]%'
    

    enter image description here

    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)
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.