1

I'm trying to comma delimit a string into rows. I made 9 columns to populate because that's the maximum amount of values that are in the string (seperated by commas and a space)

For each string formatted like: 'v1, v2, v3, v4, v5, ..., v9' I:

replace(string, ', ', '.')

Then on this resulting string (I'll call string2) I: 

ParseName(string2, 1) as v1
ParseName(string2, 2) as v2
....
ParseName(string2, 9) as v9

Since ParseName only works on the first 4 values it fails if I try to use it on a string with >4 values.

Is there another function or workaround any of you suggest?

Sam B
  • 33
  • 3
  • 3
    Yes, its limited to 4 parts as its intended to parse an SQL Server identifier which has a maximum of 4 parts, using it for anything else is a hack. What version of SQL Server are you using? – Alex K. Jun 22 '18 at 16:08
  • 1
    Well....parsename only handles 4 values. It is pretty clearly stated as such in the documenation. https://learn.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-2017 The best solution here would be to stop using delimited strings. It violates 1NF and is nothing but a pain to work with. You will need a string splitter to deal with this. Here is my favorite. http://www.sqlservercentral.com/articles/Tally+Table/72993/. Several other excellent options can be found here. https://sqlperformance.com/2012/07/t-sql-queries/split-strings – Sean Lange Jun 22 '18 at 16:10
  • if you use SQL Server 2016 or better you can use STRING_SPILT function https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017 for previous version see https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x – Dmitry Kolchev Jun 23 '18 at 17:23
  • were you able to find any solution to this? – vermachint Aug 08 '22 at 06:09

0 Answers0