** Stored Procedure to clean out Letters, Commas, Periods, and $ **

[jonnywah]

I need help writing a Stored Procedure that cleans out Letters, Commas, Periods, and $ (dollar signs) in the price field (database type: varchar) of a Product table.

The price field is in the Product table, where the primary key is ProductId. I am new to this. Please help. Thank you in advance.

 

[JayKusch]

Why do you have all these "garbage" characters in the price field to begin with. This feild should be set up as a money/smallmoney, Integer or possibly a decimal datatype. If you plan on using this field in calculation like sales, ROI ... you are in for some serious headaches in converting this varchar into a usable datatype. In saying this, you can remove the unwanted characters by using a REPLACE command as in ....

DECLARE @TestVarChar VarChar(10)

SET @TestVarChar = '$,123.00'

SET @TestVarChar = REPLACE(REPLACE(REPLACE(@TestVarChar,'$',''),',',''),'.','')





Thanks

J. Kusch

 

[jonnywah]

JayKusch,

How can I use this code in a stored procedure? I want to loop through each price record, clean out periods, commas, and $, then print out the productId of the record that was cleaned???


Thanks,
JonnyWah

 

[JayKusch]

It would be a 2 pass Stored Procedure (SP). this first part would give you the ProductIDs that will be cleaned and then the second part would actually do the clean up. This is a snippette of what the code may look like ...

Create Procedure usp_CleanIt

  AS
SELECT ProductID
FROM ProductTable
WHERE PRICE LIKE '%.%' OR '%$%' OR '%,%' OR -- ... rest of items needing to be removed  

UPDATE ProductTable
SET Price = REPLACE(REPLACE(REPLACE(@TestVarChar,'$',''),',',''),'.','')
WHERE PRICE LIKE '%.%' OR '%$%' OR '%,%' OR -- ... rest of 
items needing to be removed

Thanks

J. Kusch

 

[jonnywah]

JayKusch,

I appreciate your help. Thank you!



-Jonnywah