asked
split string in multiple columns
Hello every one, I have a very large table which has only 1 column & whose string is like **abc|1234||xyz|||||bxcvnm|rty||fgh** which I want to split in multiple columns like col1 col2 col3 col4 col5 col6 abc 1234 xyz bxcvnm rty fgh in this number of column is fixed but the delim(|) is not, can any 1 help me on this issue, cause its urgent & is for recovery.
What do you mean the delimeter is not fixed? Is it not always the pipe-character, or is it not always the same number of delimeters?
Best way to split a string is to use a Tally table - I'll refer you to [Jeff Moden's excellent article][1] about this over on SSC - but if you still need a hand, just shout - clarify some of the questions above about the delimiters and I'm sure someone here will offer some code help. [1]:
http://www.sqlservercentral.com/articles/T-SQL/62867/
As @Kev wrote in his answer, you can use tally table for string splitting, but I think it will not be so easy in your example. Especially for your example, when there are different delimiters in the string and the number of (|) as delimiter is not constant, a much better solution could be a Regular Expression pattern matching using a CLR if you use a SQL Server 2005 and above. You can take a look on my article for a sample SQL RegEx implementation[: SQL Server 2005 and SQL Server 2008 Regular Expressions and pattern matching][1] Then using the sample implementation I wrote a simple test with regular expression for your situation `(.+?)(?:\|+)(.+?)(?:\|+)(.+?)(?:\|+)(.+?)(?:\|+)(.+?)(?:\|+)(.+)` WITH Data AS ( SELECT 'abc|1234||xyz|||||bxcvnm|rty||fgh' AS val UNION ALL SELECT 'cba|||43210||||||aaaaa||bbbbb||||ee||ccccc' AS val UNION ALL SELECT 'qqq|||5555|eeee|dddddd||||||||aa||||ffffffff' AS val ) SELECT val, [1] As Col1, [2] As Col2, [3] As Col3, [4] As Col4, [5] As Col5, [6] As Col6 FROM ( SELECT d.val, r.groupId, r.value FROM Data d CROSS APPLY dbo.fn_RegExMatches(d.val, '(.+?)(?:\|+)(.+?)(?:\|+)(.+?)(?:\|+)(.+?)(?:\|+)(.+?)(?:\|+)(.+)') r WHERE r.groupId > 0 ) p PIVOT ( MAX(value) FOR groupId IN ([1], [2], [3], [4], [5], [6]) ) AS pvt and the result is: val Col1 Col2 Col3 Col4 Col5 Col6 -------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- abc|1234||xyz|||||bxcvnm|rty||fgh abc 1234 xyz bxcvnm rty fgh cba|||43210||||||aaaaa||bbbbb||||ee||ccccc cba 43210 aaaaa bbbbb ee ccccc qqq|||5555|eeee|dddddd||||||||aa||||ffffffff qqq 5555 eeee dddddd aa ffffffff [1]:
http://www.pawlowski.cz/2010/09/sql-server-2005-and-sql-server-2008-regular-expressions-and-pattern-matching-2/
+1. This is the sort of thing I was talking about in the "New Year Resolution" thread, about needing to understand some of the awesome answers that you guys come up with...
