CODEWITHSUNDEEP
sqlsql-serversubstring
Baldie47
Baldie47

Reputation: 1264

Select substring between last set of chars from last

I'm needing to extract the substring between 2 chars ( "[" and "]" ) but only for the last iteration.

here examples:

╔═════════════════════════════════════════════════════════════════════════════════════╦═══════════════════╗
║ Text                                                                                ║ Expected result   ║
╠═════════════════════════════════════════════════════════════════════════════════════╬═══════════════════╣
║ :OR1[253427815][11 Sep 2020][000685f383][Craft Labor ST][Standard]                  ║ Standard          ║
╠═════════════════════════════════════════════════════════════════════════════════════╬═══════════════════╣
║ :OR1[252348249][11 Sep 2020][0006ff85383][Craft Labor-Allowance][Skill Rate 7]      ║ Skill Rate 7      ║
╠═════════════════════════════════════════════════════════════════════════════════════╬═══════════════════╣
║ :OR1[2545528250][11 Sep 2020][00068ff5383][Craft Labor-Allowance][Attendance]       ║ Attendance        ║
╠═════════════════════════════════════════════════════════════════════════════════════╬═══════════════════╣
║ :OR1[2528454248][11 Sep 2020][000685383][Craft Labor-Allowance][Overtime]           ║ Overtime          ║
╠═════════════════════════════════════════════════════════════════════════════════════╬═══════════════════╣
║ :OR1[25254548247][11 Sep 2020][000685383][Craft Labor-Allowance][Weather Allowance] ║ Weather Allowance ║
╚═════════════════════════════════════════════════════════════════════════════════════╩═══════════════════╝

I've tried with a combination of substring and charindex:

SELECT text, SUBSTRING( text, CHARINDEX( '][', text) + 2, 11 ) AS Expected_result

but I think I should do it to search for the right and only get everything but the last "]" after finding the "][" combination

does this make sense? select form the right until finding the first ][ and then getting everything but the last ]

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One method is:

select replace(s.value, ']', '')
from t cross apply
     string_split(t.text, '[') s
where t.text like concat('%$[', s.value) escape '$';

An alternative in earlier versions:

select t.*, replace(replace(v.str, '[', ''), ']', '')
from t cross apply
     (values (stuff(t.text, 1, len(t.text) - charindex('[', reverse(t.text)), ''))) v(str);

Here is a db<>fiddle.

Upvotes: 2

Maciej Los
Maciej Los

Reputation: 8591

Try this:

SELECT REPLACE(RIGHT([text], CHARINDEX('[]', REVERSE([text]))-1), ']', '') AS Expected_result

SqlFiddle

Upvotes: 1

Related Questions