SQL: Substring until second character starting from right keeping left values

I have this filename AAAA_BBBBB_CC_HDDD_HGGG.csv and I’m trying to keep the values after the second underscore starting from the right.

So i want to keep any values just before _HDDD_HGGG.csv

This is my code:

SET @NFileN =  REVERSE(SUBSTRING(REVERSE(@source_filename),1,CHARINDEX('_',REVERSE(@source_filename), CHARINDEX('_', REVERSE (@source_filename), 0) + 1)))

And this is the returned value:

(6 rows affected)
_HDDD_HGGG.csv

Instead of being AAAA_BBBBB_CC.

Does anyone has a clue for this?

Answer

You are taking a SUBSTRING from 1 till your CHARINDEX while your string is reversed. Either reverse your string again or use LEN to find the length of your string like so:

REVERSE(
  SUBSTRING(
    REVERSE(@source_filename),
    CHARINDEX('_',
              REVERSE(@source_filename), 
              CHARINDEX('_', 
                        REVERSE (@source_filename), 
                        0)+1)+1,
    LEN(@source_filename)
  )
)

p.s.: Added a second +1 to remove the “_” between CC and HDDD

p.p.s: CHARINDEX is a SQL Server function which I assume is what you are actually using. The MySQL equivalent would be POSITION, the equivalent for LEN would be LENGTH