SQL Query to separate data into two fields

Question

I have data in one column that I want to separate into two columns. The data is separated by a comma if present. This field can have no data, only one set of data or two sets of data saperated by the comma. Currently I pull the data and save as a comma delimited file then use an FoxPro to load the data into a table then process the data as needed then I re-insert the data back into a different SQL table for my use. I would like to drop the FoxPro portion and have the SQL query saperate the data for me. Below is a sample of what the data looks like.

Store   Amount Discount
1       5.95 
1       5.95 PO^-479^2
1       5.95 PO^-479^2
2       5.95 
2       5.95 PO^-479^2
2       5.95 +CA8A09^-240^4,CORDRC^-239^7
3       5.95 
3       5.95 +CA8A09^-240^4,CORDRC^-239^7
3       5.95 +CA8A09^-240^4,CORDRC^-239^7

In the data above I want to sum the data in the amount field to get a gross amount. Then pull out the specific discount amount which is located between the carat characters and sum it to get the total discount amount. Then add the two together and get the total net amount. The query I want to write will separate the discount field as needed, see store 2 line 3 for two discounts being applied, then pull out the value between carat characters.

Answer 1

For SQL Server: You can use ChardIndex(',',fieldname) in a sql statement to find the location of the comma and then Substring to parse out the first and second field.

Answer 2

For Oracle you can use a case statement like this in your select clause. Use one for each of the two discounts:

CASE WHEN LENGTH(foo.discount) > 0 AND INSTR(foo.discount,',') > 0 THEN
SUBSTR(foo.discount,1,INSTR(foo.discount,',',1,1)) ELSE foo.discount END AS discount_column_1

Answer 3

I finally figured out exactly how to separate the fields as I need them. Below is the code that breaks the discount field into two. I can now separate the fields as needed and insert the data separated into a temp table then use a similar set of code to pull out the exact amount enclosed by the carat characters. Thanks for the help in the two answers above. I used a combination of both to get exactly what I needed.

CASE LEN(X.DISCOUNT)-LEN(REPLACE(X.DISCOUNT,',',''))
    WHEN 1 THEN SUBSTRING(X.DISCOUNT,1,CHARINDEX(',',X.DISCOUNT)-1)
    ELSE X.DISCOUNT     
END 'FIRST_DISCOUNT',
CASE LEN(X.DISCOUNT)-LEN(REPLACE(X.DISCOUNT,',',''))
    WHEN 1 THEN SUBSTRING(X.DISCOUNT,CHARINDEX(',',X.DISCOUNT)+1,LEN(X.DISCOUNT)-CHARINDEX(',',X.DISCOUNT)+1)
    ELSE ''
END 'SECOND_DISCOUNT'