How can I pull out multiple substrings from within a single string-CodePudding

For example, I have this string in an SSMS DB:

Sent for processing:1 ;DK A/C-WestlySnipes:ACCT NOT FOUND ;DK A/C-SonyaBlade:ACCT NOT FOUND

What I want to be able to do is pull out WestleySnipes and SonyaBlade from within that string and store it in a temp table. The names can be different and there can be more than one name within a particular string.

I've tried using substrings combined with CharIndex but I can only pull out 1 value. Not sure how to pull out multiple names from within the same string where the names can change in any given string.

CodePudding user response：

In SQL Server 2016 and later, STRING_SPLIT(string, delimiter) is a table valued function. You can split your text string with a ; delimiter like so. (fiddle).

SELECT value FROM STRING_SPLIT(
   'Sent for processing:1 ;DK A/C-WestlySnipes:ACCT NOT FOUND ;DK A/C-SonyaBlade:ACCT NOT FOUND',
   ';')

You get back this:

value
Sent for processing:1
DK A/C-WestlySnipes:ACCT NOT FOUND
DK A/C-SonyaBlade:ACCT NOT FOUND

Then you can use ordinary string-editing functions on value in your query to extract the precise substrings you need. Something like this will work for your specific example. (fiddle.)

SELECT 
  value,
  REPLACE(REPLACE(value, 'DK A/C-', ''), ':ACCT NOT FOUND', '') val
  
FROM STRING_SPLIT(
   'Sent for processing:1 ;DK A/C-WestlySnipes:ACCT NOT FOUND ;DK A/C-SonyaBlade:ACCT NOT FOUND',
   ';')
WHERE value LIKE 'DK %'