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

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?

CodePudding user response：

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.

CodePudding user response：

You were close. You need to use CHARINDEX to also find the position of the second underscore.

SELECT SUBSTRING(FileName,
                 CHARINDEX('_', FileName)   1,
                 CHARINDEX('_', FileName, CHARINDEX('_', FileName)   1) -
                     CHARINDEX('_', FileName) - 1) AS FilePart
FROM yourTable;

CodePudding user response：

Here's a way using STRING_SPLIT and FETCH, rather than SUBSTRING We split the string and only return the second row

SELECT
   value
FROM   STRING_SPLIT('73186_RHIMagnesita_PHI_StopLoss_TruncSSN_NonRedact_Inc_to_Apr2022_Paid_to_Apr2022_EDIT','_')
ORDER BY (SELECT NULL)
OFFSET 1 ROWS 
FETCH NEXT 1 ROWS ONLY;

Note: On Azure Sql Server STRING_SPLIT has an ordinal parameter, so you could write this

SELECT
value
FROM
STRING_SPLIT('73186_RHIMagnesita_PHI_StopLoss_TruncSSN_NonRedact_Inc_to_Apr2022_Paid_to_Apr2022_EDIT','_', 1)
WHERE ordinal = 2