April 13, 2016 at 12:30 pm
I have data in a string column such as $154,111.85
My task is to reduce this string to 154111.85 The string values can range from $1,000.00 to $700,000.00
Is there a simple way to do this?
April 13, 2016 at 1:02 pm
Nilssond (4/13/2016)
I have data in a string column such as $154,111.85My task is to reduce this string to 154111.85 The string values can range from $1,000.00 to $700,000.00
Is there a simple way to do this?
You provided the answer in your question title. REPLACE.
REPLACE(REPLACE(YourString, '$', ''), ',', '')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 13, 2016 at 1:03 pm
The next question is why are you storing dollar amounts as formatted text instead of decimals?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 13, 2016 at 1:11 pm
Sean Lange (4/13/2016)
The next question is why are you storing dollar amounts as formatted text instead of decimals?
Agreed. Your original question asked for a simple way to convert you data. The simplest way would have been to use the correct data type from the get-go. Or, consider changing your data type now. Storing money values as varchar will cause you many headaches. Your are experiencing one of those now. Just think of what you would have to do in order to perform math logic on that column. Any attempt to sum, average, etc. on that string value would mean you'd have to convert it every time, every query.
April 13, 2016 at 1:29 pm
Below is how you'd do it if the data is stored as money or varchar...
-- if stored as money data type
DECLARE @x money = '$154,111.85';
SELECT CAST(@x AS decimal(10,2));
GO
-- if stored as varchar
DECLARE @x varchar(20) = '$154,111.85';
SELECT CAST(CAST(@x AS money) AS decimal(10,2));
If there is a possibility of strings that wont convert to money or numeric you could use PATEXCLUDE8K (referenced in my signature) like so:
DECLARE @x TABLE (someID int identity, someValue varchar(100));
INSERT @x VALUES ('0.11'),('$100.002'),('$150,456.11'),('>>>$154,111.85!!!<<<');
SELECT ISNULL(TRY_CONVERT(money,someValue),NewString)
FROM @x
CROSS APPLY dbo.PatExclude8K(someValue, '[^0-9.]')
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply