0

I have a table (AW) with a field in which I need to parse out everything between the pipe '|' for each RecordID and find out if any of the parses start with an S. Since this field has a varied amount of strings between the pipes '|' how would I programatically do this without assuming the number of parses need to be done.

select RecordID, DealID from AW

|-RecordID-|------------------------DealID----------------------------------|
3000449*1   66411/10*ACME01*PLI*PY**|94084/10*ACME01*PLI*PY**
2703781*90  91715/10*P8*PLI*FFR**CS|91719/10*P8*PLI*FFR**CS
2703781*7   91715/10*P8*PLI*FFR**CS|91719/10*P8*PLI*FFR**CS|S118849.13/10**PM*22076*01*CS
CM2903835*1 106834/10*FT*PM*FT*01*CS|108807/10*FT*PM*FT*01*CS
3000801*19  S124374.52/10*DM*PM*00080*01*CS
3004043*9   91714/10*P8*PLI*DR**CS|84767/10*P8*PLI*DR**CS
3004043*130 S119765.34/10*P8*PM*38808*01*CS|91714/10*P8*PLI*DR**CS|84767/10*P8*PLI*DR**CS

This is using SQL Server

precose
  • 141
  • 1
  • 10
  • 2
    You need to use a string splitter. Here is my personal favorite. http://www.sqlservercentral.com/articles/Tally+Table/72993/ And several other excellent options can be found here. https://sqlperformance.com/2012/07/t-sql-queries/split-strings – Sean Lange Nov 29 '17 at 14:17
  • 1
    You need to re-design your table. Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** – Zohar Peled Nov 29 '17 at 14:54
  • @SeanLange This was exactly what I needed. Thank you Sean. – precose Nov 29 '17 at 18:36

0 Answers0