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.

are you able to define a pattern describing the substrings you want to extract? Are the substrings always at the same position in the main string? Are you trying to select any row that contains those two substrings? Or extract the content of a particular pattern of substrings for all rows? — encrest
– encrest, Commented Jul 2, 2015 at 21:10
How do you define generically when the 1st substring starts & ends, as well as the 2nd? Do they always start with "T00BE" and "T8LQI", respectively? — Brent D
– Brent D, Commented Jul 3, 2015 at 0:39

user2221088 · Accepted Answer · 2015-07-03 01:26:18Z

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)

ThePravinDeshmukh · Accepted Answer · 2015-07-03 07:03:06Z

2

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

answered Jul 3, 2015 at 7:03

ThePravinDeshmukh

1,92313 silver badges23 bronze badges

Collectives™ on Stack Overflow

A SQL Query to select two substrings from a known string

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related