Split 1 field into multiple columns

Question

question

wolfeste asked Apr 8, '15

Split 1 field into multiple columns

I have a field in my database that users input data that i would need to be put into different columns for a report I am building. Does anyone know a way to have the data split into separate columns? The data is normally inputted in the example below. I could also have the users put in a special character if that helps as well. So for each row i would need it in a different column. For this example it would go to 3 columns. INV #2168 PD W COMCHECK INV #240018998 ON LTC ACCT INV #240998858 ON LTC ACCT

sql-server-2012

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sateesh commented · Apr 09, 2015 at 12:39 AM

Can you rprovide an example of input and output you are expecting

0 ·

rsheik commented · Apr 09, 2015 at 11:05 AM

Use Char Index to find your special character position dynamically. I think your first special character is **#** and second is **a space**

0 ·

wolfeste commented · Apr 09, 2015 at 12:33 PM

I will elaborate on this more The data looks like this: INV #2168 PD W COMCHECK ; INV #240018998 ON LTC ACCT ; INV #240998858 ON LTC ACCT ; I will be using ; to be the "separator" (looking to do something very similar to comma delimited file in excel). I want the query to put the data into separate columns like this: COL1 COL2 COL3 INV #2168 PD W COMCHECK INV #240018998 ON LTC ACCT INV #240998858 ON LTC ACCT

0 ·

wolfeste commented · Apr 09, 2015 at 01:26 PM

Would you have an example that would seperate by the symbol # or ;

0 ·

Answer 1 · 2015-04-09T22:39:24Z

Arcanas answered Apr 9, '15

This is a quick and dirty example, assuming you only have three columns of data. If it was to be a dynamic number of columns, that would complicate things somewhat :) DECLARE @tmpTable TABLE ( FieldName NVARCHAR(512) ) INSERT @tmpTable ( FieldName ) VALUES ( 'INV #2168 PD W COMCHECK ; INV #240018998 ON LTC ACCT ; INV #240998858 ON LTC ACCT ;' ), ( 'INV #2168 PD W Testing1 ; INV #240018998 ON LTC ACCT ; INV #240998858 ON LTC ACCT ;' ), ( 'INV #2168 PD W LONGSTRING2 ; INV #240018998 ON LTC ACCT ; INV #240998858 ON LTC ACCT ;' ); WITH CTE_Indexes AS ( SELECT TT.FieldName, CHARINDEX(';', TT.FieldName) AS FirstIndex, CHARINDEX(';', SUBSTRING(TT.FieldName, CHARINDEX(';', TT.FieldName) + 1, LEN(TT.FieldName))) AS SecondIndex FROM @tmpTable TT ) SELECT LTRIM(RTRIM(SUBSTRING(TT.FieldName, 1, CI.FirstIndex - 1))) AS Col1, LTRIM(RTRIM(SUBSTRING(TT.FieldName, CI.FirstIndex + 1, CI.SecondIndex - 1))) AS Col2, LTRIM(RTRIM(SUBSTRING(TT.FieldName, CI.FirstIndex + CI.SecondIndex + 1, LEN(TT.FieldName) - CI.FirstIndex - CI.SecondIndex - 1))) AS Col3 FROM @tmpTable TT INNER JOIN CTE_Indexes CI ON CI.FieldName = TT.FieldName

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

wolfeste commented · Apr 10, 2015 at 12:13 PM

Thank you! that is what I am looking for.

0 ·

Arcanas commented · Apr 10, 2015 at 02:30 PM

One thing to note, when I was testing it, is if you have two rows with the same data it will break, as I am joining on the text field. You could add an ID field of some sort to prevent that

0 ·

wolfeste commented · Apr 10, 2015 at 03:06 PM

The script you provided works great with the static informaiton, but once I applied this to a query to pull a few 100 rows it now gives Invalid length parameter passed to the LEFT or SUBSTRING function. Would you know what is causing this?

0 ·

Arcanas commented · Apr 10, 2015 at 07:23 PM

Could be something weird with the math, causing a -1 or something similar. Try grabbing the results from the CTE only, see if there is a weird number in there somewhere. Could be as simple as a : instead of a ;, or something similar to that.

0 ·

question