0

I have the following string of characters:

594074_Ally_Financial_TokioMarine_MD_SLDET_20210101_20211130_20211208

I am attempting to extract everything after the first '_' but before the '_TokioMarine', so the final string will look like:

Ally_Financial

Is this possible to do with SQL? I attempted but it was pulling the incorrect characters. I cant get the ones in between the values specified.

SELECT
@CurPolicyHolder = Right( DFH.FileName, CHARINDEX('_', DFH.FileName) - 1)
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Austin
  • 1
  • 3
  • 1
    Are you looking for [the `SUBSTRING` function](https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15)? Also, you should tag your specific DBMS, since the answer may depend on it. – Richard Deeming Jan 11 '22 at 16:24
  • Substring may be the case, im not sure how to apply it though but I am using SQL Server – Austin Jan 11 '22 at 16:28
  • Is the `_TokioMarine` a fixed value? Or are you looking for everything between the first and third `_` characters? – Richard Deeming Jan 11 '22 at 16:31
  • Its a fixed value – Austin Jan 11 '22 at 16:33
  • Does this answer your question? [Extract data between characters SQL](https://stackoverflow.com/questions/70609080/extract-data-between-characters-sql) – Charlieface Jan 11 '22 at 17:47

1 Answers1

0

To extract everything between the first _ character and the _TokyoMarine string, you can use:

SELECT
    @CurPolicyHolder = SUBSTRING(DFH.FileName, CHARINDEX('_', DFH.FileName) + 1,
 CHARINDEX('_TokioMarine', DFH.FileName) - CHARINDEX('_', DFH.FileName) - 1)

SUBSTRING (Transact-SQL)
CHARINDEX (Transact-SQL)

Richard Deeming
  • 29,830
  • 10
  • 79
  • 151