0

I'm using SQL Server 2014. I have 1 field that contains a string value like this

ADGA;BADF;CASD;DFAD;SADAF;gNDG;asDD;dADA;zzz;SDFS;SFGG;SSFG;DFGF

How do I SELECT to return the string between the 10th and 11th ; ?

The values between the semi colon's is always 4 characters in length.

So from the above, I need to return:

SFGG
Michael
  • 2,507
  • 8
  • 35
  • 71
  • Use: https://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string to find the 10th and 11th positions and then use `substring()`.. or write your own functions such as: https://www.resultdata.com/sql-server-finding-the-nth-instance-of-a-string/ – xQbert Oct 11 '19 at 14:13
  • You can't easily do this on SQL Server, and so you might want to handle such CSV data outside of your database. – Tim Biegeleisen Oct 11 '19 at 14:15
  • @TimBiegeleisen this is not CSV data, this is literally the contents of one field – Michael Oct 11 '19 at 14:16
  • 1
    I know that :-) ... it is unnormalized semicolon separated data, which should not be stored in a single database column. SQL Server, among the major database vendors, is particularly poorly-suited to handle your requirement. – Tim Biegeleisen Oct 11 '19 at 14:18
  • I would steer clear of that first link from xQbert...looping for this is horribly inefficient. There are several set based methods of splitting strings that are more efficient. – Sean Lange Oct 11 '19 at 14:19
  • @TimBiegeleisen ok no problem – Michael Oct 11 '19 at 14:21
  • And, in addition, look up `delimitedsplit8k_LEAD` – Thom A Oct 11 '19 at 14:22
  • 1
    The accepted answer on the duplicate is another looping splitter...ugh. See [this article](https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2) for a much better solution to this problem. – Sean Lange Oct 11 '19 at 14:24
  • 2
    @Michael Here is a simple example https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=04c1ab337b85672cb45762a6c6029dc3 – John Cappelletti Oct 11 '19 at 14:24
  • Yeah, the upvoted answer is the better one there, @SeanLange . There is also a `STRING_SPLIT` answer. Unfortunately, as many of us know, the accepted answer is not always the best answer. – Thom A Oct 11 '19 at 14:26
  • @Larnu agreed 100%. I know that many people just look for the accepted answer and don't bother with understanding the rest of them. :D – Sean Lange Oct 11 '19 at 14:29
  • Mabe something like this: https://rextester.com/LSQ6157 but it uses a recursive CTE to get there.. – xQbert Oct 11 '19 at 14:39
  • That's an rCTE, @xQbert and is also a form of RBAR (or looping). A tally (like dbo.DelimitedSplit8K` uses) or an XML/JSON splitter would be far better performance wise. – Thom A Oct 11 '19 at 14:40
  • i agree upvoded john's comment – xQbert Oct 11 '19 at 14:40

0 Answers0