December 24, 2005 at 3:04 am
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
December 24, 2005 at 12:31 pm
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
Change is inevitable... Change for the better is not.
December 24, 2005 at 6:33 pm
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
December 25, 2005 at 7:13 am
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
Change is inevitable... Change for the better is not.
December 25, 2005 at 11:50 am
Thanks That worked Great
December 25, 2005 at 9:25 pm
You bet... Merry Christmas.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy