sqlsubstringextractcharindex

How can I use SQL Substring to extract characters from this filename?

I'm attempting to use the SUBSTRING function to extract a name out of a filename.

An example filename would be: "73186_RHIMagnesita_PHI_StopLoss_TruncSSN_NonRedact_Inc_to_Apr2022_Paid_to_Apr2022_EDIT" I'm attempting to extract the "RHIMagnesita" from this filename.

The substring I used was:

SUBSTRING(DFH.FileName, CHARINDEX('_', DFH.FileName) + 1, CHARINDEX('_PHI', DFH.FileName) - 1)

The results it gave were: "RHIMagnesita_PHI_S"

How do I extract only "RHIMagnesita" using the Substring function?

Solution

  • The third parameter in SUBSTRING is length not position, so you would need to substract the length of the beginning string.

    SUBSTRING(DFH.FileName, CHARINDEX('_', DFH.FileName) + 1, CHARINDEX('_PHI', DFH.FileName) - CHARINDEX('_', DFH.FileName))

    You might need to add or substract 1, but that's the idea.