Extract value before one symbol and stop after another one in SQL

Question

Post reply

Extract value before one symbol and stop after another one in SQL

JeremyU

SSChasing Mays

Points: 640
More actions
August 22, 2023 at 11:30 pm

#4268548

Hello,
I am trying to extract Event Name from a long string that has some information not relevant for what I am doing at the very moment. I basically need to extract everything after 2nd '_' and before 3rd '_'.
So technically I need to get Bestseller and Promotion for the examples that I am showing in the picture.
I believe we usually can extract such things with SUBSTRING, but here the number of characters before and after is unknown.
SELECT rl_event_id
Extract value after 2nd '_' and stop before 3rd '_'
Please let me know if someone has ideas.

As for charindex function, I am not 100% sure how I can use it in this situation.
It gives me 0 or 16.
When I do it second time it just gives me 0s.

There must be another way to do it. I am not sure how I can approach it in a different way.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246956 More actions · Answer 1

Here is a possibility.

You could so it all in a single (rather lengthy) expression, but I broke it out to help you understand how I did it.

Please provide consumable test data in future.

WITH SomeText
AS (SELECT t = 'AB123456_BlahBlah_BESTSELLER_blahBlah')
SELECT SomeText.t
      ,c1.t1
      ,c2.t2
      ,Ext = LEFT(c2.t2, CHARINDEX ('_', c2.t2) - 1)
FROM SomeText
    CROSS APPLY
(SELECT t1 = STUFF (SomeText.t, 1, CHARINDEX ('_', SomeText.t), '')) c1
    CROSS APPLY
(SELECT t2 = STUFF (c1.t1, 1, CHARINDEX ('_', c1.t1), '')) c2;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Michael L John One Orange Chip Points: 27054 More actions · Answer 2

Idea#1

Are these values known? It looks like there is a "fixed list" of possible values. If so, then you can skip parsing the string and do a like. The issue here is that the leading wildcards may be a performance issue.

CASE
  WHEN String LIKE '%Bestseller%' THEN 'Bestseller'
  WHEN String LIKE '%Promotion%' THEN 'Promotion'

Idea #2

Leverage Jeff Moden's string splitter HERE.

SELECT * 
FROM [dbo].[DelimitedSplit8K] (
   'ABC_123_XYZ_789'
  ,'_')
WHERE ItemNumber = 3

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Phil Parkin SSC Guru Points: 246956 More actions · Answer 3

I vote for using the splitter. For some reason, I'd forgotten that even existed when I posted earlier.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101087 More actions · Answer 4

I think CHARINDEXes might be somewhat more efficient here:

;WITH data AS (
    SELECT rl_event_id = 'AB123456_BlahBlah_BESTSELLER_blahBlah'
)
SELECT rl_event_id, SUBSTRING(rl_event_id, pos_of_second_underscore + 1, pos_of_third_underscore - pos_of_second_underscore - 1)
FROM data
CROSS APPLY ( SELECT CHARINDEX('_', rl_event_id) ) AS ca1(pos_of_first_underscore)
CROSS APPLY ( SELECT CHARINDEX('_', rl_event_id, pos_of_first_underscore + 1) ) AS ca2(pos_of_second_underscore)
CROSS APPLY ( SELECT CHARINDEX('_', rl_event_id, pos_of_second_underscore + 1) ) AS ca3(pos_of_third_underscore)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".