-1

I have a table which has a column with a long string, i'm tasked with parsing an ID from the string, i've gotten to the point where I can extract everything after a delimiter string, but my query is still catching everything after the delimiter string, which I don't need.

There are records where have multiple ID's on the same line that I need to separate and capture as well.

I've included the SQL fiddle:

fiddle

For some reason on the live data, when I use the query I have provided, it doesn't always capture the account number.

Here is an updated SQL Fiddle to show the new problems I'm running into. Updated FIDDLE

seequill
  • 33
  • 7
  • What flavour of SQL are you using? T-SQL, Oracle? The code doesn't make it clear what you want either. – Reeza Mar 20 '20 at 21:16
  • @Reeza T-Sql, I just want to extract the account number from the column. In the case where there are two account numbers, i'd like to output both of them separately. – seequill Mar 20 '20 at 21:18
  • Does this answer your question? [A SQL Query to select a string between two known strings](https://stackoverflow.com/questions/18362260/a-sql-query-to-select-a-string-between-two-known-strings) – Eric Brandt Mar 20 '20 at 21:22

1 Answers1

1

This returns the account numbers in the data:

select s.value
from temp t CROSS APPLY
     (VALUES (STUFF(description, 1, CHARINDEX('Account Attached: ', description) + LEN('Account Attached: '), '')
             )
     ) v(aa) CROSS APPLY
     STRING_SPLIT(LEFT(v.aa, CHARINDEX('Next Steps:', v.aa) - 2), ' ') s;

Here is your SQL Fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks!! This seems to work on SQL Fiddle, but when I try to apply it in the live environment, i get Invalid object name 'STRING_SPLIT'. I'm assuming my version of SQL isn't compatible with that function. – seequill Mar 20 '20 at 21:29
  • What version of SQL server do you have? It's valid on 2016+ – Reeza Mar 20 '20 at 21:36
  • @Reeza I'm using SSMS 18. Not sure why it isn't working either. The only thing I changed was 'temp' to the actual table. – seequill Mar 20 '20 at 21:41
  • What do you get when you run `select @@version`? You can connect to database prior to you SSMS version. Or maybe the compatibility is set back to a prior version? – Isaac Mar 20 '20 at 21:46
  • 1
    @Isaac well..it looks like i'm only on 2014. – seequill Mar 20 '20 at 21:50
  • @seequill . . . Your options are (1) upgrade SQL Server; (2) download a UDF for splitting a string; (3) using a recursive CTE; (4) Using XML tricks to split the string. – Gordon Linoff Mar 20 '20 at 22:48
  • @GordonLinoff this excel is actually a mess...since it's a user inputted field there are rows that have a comma in between multiple account numbers, then are are some with spaces in between multiple account numbers. Some rows do not have a string delimiter, so it's throwing a length error on those ones. I'm able to parse through some of them and get an account number (roughly 300 out of 650), but of those 300 I run into the comma and space issue where multiple account numbers occur. – seequill Mar 20 '20 at 23:17