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

Question

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.

Look at this question: stackoverflow.com/questions/35707770/… — Icemanind
– Icemanind, Commented Jan 12, 2023 at 19:56
SSMS is an IDE like application, it does store any thing, especially not databases. What RDBMS are you using? SQL Server, Azure SQL Database, Azure Synapse, something else? SSMS 16 came out alongside SQL Server 2016, and supported SQL Server 2005-2016 along with a few other Microsoft products. Why, as well, haven't you updated to SSMS 18? SSMS 19 is about to come out of preview too. — Thom A
– Thom A ♦, Commented Jan 12, 2023 at 20:01
T-SQL, however, has very weak string manipulation skills. You would be far better off normalising your data and having the application passing the data is a well structured format to that normalised design. — Thom A
– Thom A ♦, Commented Jan 12, 2023 at 20:04
I've added that tag instead then. Adding the version you are using (as a tag) would help too. — Thom A
– Thom A ♦, Commented Jan 12, 2023 at 21:02

O. Jones · Accepted Answer · 2023-01-12 21:36:46Z

1

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 %'

answered Jan 12, 2023 at 21:36

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NanaDing Over a year ago

Fantastic! I think this should do it. Thank you @O.Jones

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related