October 20th, 2003, 12:50 PM
Splitting out character strings
Help! I am working with an off-the-shelf shopping cart and now have the data in a MS SQL Server. All of our product options are loaded into one column separated with brackets like this: [Gift Wrap: Birthday] [Frame Color: Black] Each string has the option type (ie Frame Color) and then what the customer selected (ie Black). I need to be able to separate out the options for querys etc.
I have tried to use the charindex and substring functions which gets very complicated and I can't seem to find the end bracket:
SELECT OrderID, Substring(Options, charindex('[Gift Wrap:',Options)+11, charindex('][',Options, charindex('[Gift Wrap:',Options)+11)) AS "GiftWrap"
I was also wondering if I can somehow create new columns that can hold the parsed data.
I sure would appreciate some help.
November 14th, 2003, 12:09 PM
what you probably need to use is the substring function with patindex. something like:
select substring( options, patindex(%:%, options), datalength(options) - patindex(%:%, options) - 1)
also, are you looking to create new columns in the order details table to hold the data? it might be easier to use variable tables to do this instead.