question

mlawton avatar image
mlawton asked

Parsing Data in a column from a sql table

I have data in a table in a column called Fruit with the following data:

Fruit                                                     
Lemons cost $99.99 on sale                                 
$5.99 Apples are on sale                                  
Where are the $65.99 lemons                    

I want to be able to update the table with a column called Amount and just have the following from the fruit column:

Amount                                                     
$99.99                                 
$5.99                                  
$65.99                    

Can this be done?

Thanks

sql-server-2005
10 |1200

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

Benjamin avatar image
Benjamin answered

This UPDATE statement will work by taking the price starting at the dollar sign $ and so long as there is a space or the end of the field data. It will place the string (including the dollar sign) into a character field (I assumed varchar). If you want to insert the data into a numeric field then you'll want to adjust the SUBSTRING statement to omit the dollar sign. Let me know if you need additional help.

UPDATE Pricing            
SET Amount =            
    CASE WHEN CHARINDEX('$', Fruit) > 0            
    	THEN SUBSTRING(Fruit,CHARINDEX('$',Fruit),CHARINDEX(' ',SUBSTRING(Fruit,CHARINDEX('$',Fruit),Len(Fruit))+' '))            
    	ELSE NULL            
    END            

Here's a SELECT statement (with comments) showing how the SUBSTRING and CHARINDEX work together to parse the string. Again, I am assuming your table is called Pricing. Just change this after the FROM to make it work for you.

SELECT Fruit, Amount,            
    CASE WHEN CHARINDEX('$', Fruit) > 0 --search for the position of the dollar sign ( $ )            
    	THEN             
    		SUBSTRING(Fruit,            
    			CHARINDEX('$', Fruit), --start at the $            
    			CHARINDEX(' ', --now find the first space            
    				SUBSTRING(Fruit, CHARINDEX('$', Fruit), Len(Fruit)) + ' ') --after the $, NOTE: be sure to add a space in case the Amount comes at the end of the phrase            
    			)            
    	ELSE NULL --don't try the substring operation if we can't find the $            
    END AS TestAmount            
FROM Pricing            
10 |1200

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

Madhivanan avatar image
Madhivanan answered

Another method

Updated based on the comment by Benjamin

select fruit,substring(amount,1,patindex('%[0-9][ ]%',amount)) from            
(            
SELECT Fruit,             
    substring(fruit,charindex('$',fruit),len(fruit)) as amount            
FROM             
(            
    select '9 Lemons cost 67 $99.99 on sale' as fruit union all            
    select '$5.99 Apples 877 are on sale' union all            
    select 'Where are the $65.99 lemons 7856'             
)as Pricing            
) as t            
10 |1200

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

mlawton avatar image
mlawton answered

I would like to accept Benjamin's answer as the solution. However, I don't see anywhere on this page to do that. I looked for a checkmark on the left and I do not see it. Also, I could not add this as a comment instead of Your Answer.

10 |1200

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

Kristen avatar image
Kristen answered
            
SELECT  LEFT([Temp], PATINDEX('%[^0-9.]%', [Temp])-1), [Fruit]            
FROM            
(            
    SELECT [Temp] = SUBSTRING([Fruit], CHARINDEX('$', Fruit) + 1, LEN([Fruit]))            
    		+ ' ', [Fruit]            
    FROM            
    (            
    	SELECT [Fruit] = 'Lemons cost $99.99 on sale' UNION ALL            
    	SELECT '$5.99 Apples are on sale' UNION ALL            
    	SELECT 'Where are the $65.99 lemons' UNION ALL            
    	SELECT 'Lemons cost $99.99x on sale' UNION ALL            
    	SELECT '$5.99x Apples are on sale' UNION ALL            
    	SELECT 'Where are the $65.99x' UNION ALL            
    	SELECT 'Ends with $75.99' UNION ALL            
    	SELECT 'Ends with $75.99x'            
    ) AS Y            
) AS X            
10 |1200

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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