Split a column into multiple columns and insert results into table

Question

Split a column into multiple columns and insert results into table

Josh Quisling

SSC Enthusiast

Points: 179
More actions
December 24, 2005 at 3:04 am

#166280

I have data that is stored in a table column that I need to parse and insert into different palces in the database.
Here is what I am trying to do:
In the Subject column I have the following data: John Smith #PD $300/1023
I need to split this one field and move the data into the following columns
Name: John Smith
Paid: #PD
Amount: $300
Check Number: 1023
I have seen PARSENAME as an option but I can't figure out how to set a variable as the column and then insert the results into a table.
Any help would be wonderful.
Thanks
Josh

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 1

Assuming that the column you are parsing is called "ParseThis" and your table name is called "yourtable", this will work PROVIDED that you always have all 4 parts...

SELECT RTRIM(SUBSTRING(ParseThis,1,CHARINDEX('#',ParseThis,1)-1)) AS Name,

RTRIM(SUBSTRING(ParseThis,CHARINDEX('#',ParseThis,1),

CHARINDEX('$',ParseThis,1)-CHARINDEX('#',ParseThis,1))) AS Paid,

RTRIM(SUBSTRING(ParseThis,CHARINDEX('$',ParseThis,1),

CHARINDEX('/',ParseThis,1)-CHARINDEX('$',ParseThis,1))) AS Amount,

RTRIM(SUBSTRING(ParseThis,CHARINDEX('/',ParseThis,1)+1,999)) AS [Check Number]

FROM yourtable

PARSENAME certainly is a viable option but would take as much work because it only parses on the (.) character. Alother alternatives include saving the unparsed column in a text file and using either BCP or Bulk Insert, in conjunction with a format file, to parse the data for you. Again, that method would require that all 4 parts be available (as determined by the delimiters of #, $, and /)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Josh Quisling SSC Enthusiast Points: 179 More actions · Answer 2

That works to split up the field but how do I take the split information and insert it back into the database.

So I have a table setup with the following columns

Subject - is where the blob of data that we split is

Name - is where the name will be inserted

Paid - is where paid will be inserted

Amount - is where amount will be inserted

checkNO - is where Check Number will be inserted.

Thanks

Josh

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

You are new at this...

Try this, Josh...

UPDATE yourtable

SET Name = RTRIM(SUBSTRING(Subject,1,CHARINDEX('#',Subject,1)-1)),

Paid = RTRIM(SUBSTRING(Subject,CHARINDEX('#',Subject,1),

CHARINDEX('$',Subject,1)-CHARINDEX('#',Subject,1))),

Amount = RTRIM(SUBSTRING(Subject,CHARINDEX('$',Subject,1),

CHARINDEX('/',Subject,1)-CHARINDEX('$',Subject,1))),

CheckNo = RTRIM(SUBSTRING(Subject,CHARINDEX('/',Subject,1)+1,999))

FROM yourtable

And, let's hope that subject isn't really a "BLOB"... "Blob" is an Oracle term for a binary large object. The equivalent in SQL Server would be "Binary" and the above won't work on a Binary. I'm hoping that "Subject" is a VARCHAR or CHAR but you never said.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Josh Quisling SSC Enthusiast Points: 179 More actions · Answer 4

Josh Quisling

SSC Enthusiast

Points: 179

December 25, 2005 at 11:50 am

#611400

Thanks That worked Great

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

You bet... Merry Christmas.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)