How to extract string in a cell between the 4th underscore and the 5th underscore in a select statement in SQL

Question

Example:

E9393_89df8_PRODUCT1_VARIATION4_COLOR6_iwantthis_20220101_STYLE54
E5464_7gf67_PRODUCT9_VARIATION65_COLOR16_iwantthistoo_20230109_STYLE50
E9875_grde67_PRODUCT13_VARIATION41_COLOR49_iwantthisaswell_20230113_STYLE64

i've tried charindex but can't figure out how to get what I need

E9393_89df8_PRODUCT1_VARIATION4_COLOR6_iwantthis_20220101_STYLE54
E5464_7gf67_PRODUCT9_VARIATION65_COLOR16_iwantthistoo_20230109_STYLE50
E9875_grde67_PRODUCT13_VARIATION41_COLOR49_iwantthisaswell_20230113_STYLE64

should turn into

iwantthis
iwantthistoo
iwantthisaswell

Answer 1

You can use a combination of CHARINDEX and SUBSTRING. However, it gets messy as you have to pass the same truncated string on each search.

Here it's an example of how to do it (each column contains the steps so you can see how it works)

SELECT *, SUBSTRING(column1, CHARINDEX('_',column1)+1,200) AS Remove1
    ,SUBSTRING( 
        SUBSTRING(column1, CHARINDEX('_',column1)+1,200) 
        , CHARINDEX('_', SUBSTRING(column1, CHARINDEX('_',column1)+1,200)  )+1, 200) AS Remove2
    ,SUBSTRING( 
        SUBSTRING( 
                SUBSTRING(column1, CHARINDEX('_',column1)+1,200) 
                , CHARINDEX('_', SUBSTRING(column1, CHARINDEX('_',column1)+1,200)  )+1, 200)
                ,CHARINDEX('_',SUBSTRING( SUBSTRING(column1, CHARINDEX('_',column1)+1,200) , CHARINDEX('_', SUBSTRING(column1, CHARINDEX('_',column1)+1,200)  )+1, 200) ) +1, 200) AS Remove3
FROM #temp

Answer 2

Details depend on your DBMS. In MySQL v8 you can use a regular expression such as SELECT REGEXP_SUBSTR(columnName, '_.*?_', 1, 3) FROM tableName;

The pattern is surrounded by your underscores. The .* says match anything. The question mark says only go as far as you need to. The 1 says start at the beginning of the column value. The 3 picks the match you want (which is less than obvious for reasons I'll elide).

Note that, by my count, the values between the 4th and 5th underscores are COLOR. Whatyouwant seems to be between underscores 5 and 6.