1

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
NanaDing
  • 37
  • 5
  • Look at this question: https://stackoverflow.com/questions/35707770/sql-parse-out-multiple-substrings – Icemanind Jan 12 '23 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 Jan 12 '23 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 Jan 12 '23 at 20:04
  • @Larnu - Yes it is SQL Server. – NanaDing Jan 12 '23 at 20:52
  • I've added that tag instead then. Adding the version you are using (as a tag) would help too. – Thom A Jan 12 '23 at 21:02
  • Please [edit] your question to specify how you want those CamelCase names extracted from the longer string. One could guess from your example, but, hey, with respect that's your job not ours. – O. Jones Jan 12 '23 at 21:09
  • @O.Jones - I would like to pull those names (WestlySnipes and SonyaBlade) as a SELECT statement. This was what I've tried so far but it's only pulling the very first name: select SUBSTRING(column1,CHARINDEX('DK A/C-',column1)+7,CHARINDEX(':ACCT NOT FOUND',column1)-CHARINDEX('DK A/C-',column1)-7) From tablename where column1 like '%acct not found%' In the end I'd like to pull "WestlySnipes" and "SonyaBlade" into a temp table to be used elsewhere. – NanaDing Jan 12 '23 at 21:18
  • Right. What's the spec for where the names start and end in the longer text string? – O. Jones Jan 12 '23 at 21:22
  • Edit to my comment above: I'd like to pull the names with each name on its own row so I can use it elsewhere – NanaDing Jan 12 '23 at 21:23
  • @O.Jones - The name to pull starts right after "DK A/C-" and ends before the ":ACCT NOT FOUND". This will be consistent across all the names that I need to pull. There can be 1 or more names in a particular string. In my example above there are 2 names that need to be pulled. – NanaDing Jan 12 '23 at 21:26

1 Answers1

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 %'
O. Jones
  • 103,626
  • 17
  • 118
  • 172