-1

I'm trying to remove dollar signs and commas to one column in my table. Right now, the values are casted as varchar and I want to change it to float. Some examples of values: '$5,000','-$5,000','$-' and the last value needs to change to 0.

I tried writing replace statements but not sure how to retain the negative values. Any ideas?

Derek Frank
  • 15
  • 1
  • 5
  • 1
    What version of SQL Server are you using? Type `SELECT @@VERSION` into your SSMS and add the return results to your post. – dfundako Oct 11 '19 at 13:46
  • 4
    @SQLDiver: whether or not `MONEY` is a good idea or not is debatable, but the type is not deprecated (as, in scheduled for removal in a future version of SQL Server, the way `TEXT` is). – Jeroen Mostert Oct 11 '19 at 13:48
  • 3
    @SQLDiver I am not a huge fan of the Money datatype but it is NOT deprecated. https://learn.microsoft.com/en-us/sql/t-sql/data-types/money-and-smallmoney-transact-sql?view=sql-server-2017 – Sean Lange Oct 11 '19 at 13:51
  • 2
    Dare I ask, what's wrong with `REPLACE(YourVarcharMoney,'$','')`? – Thom A Oct 11 '19 at 13:54

3 Answers3

1

The following will work in SQL Server 2012+:

DECLARE @Amount AS VARCHAR(100)

SET @Amount = '$5,000'

SELECT
  TRY_PARSE(REPLACE(@Amount, '$', '') AS NUMERIC(10,2))

The dollar signs are removed using the replace function. TRY_PARSE() accepts the commas and returns null if there are other erroneous characters.

NUMERIC is preferable to FLOAT for currency values. See more here for differences between the types: Difference between numeric, float and decimal in SQL Server

SQLDiver
  • 1,948
  • 2
  • 11
  • 14
0

Assuming you are using at least SQL Server 2012, you can use TRY_PARSE()

SELECT 
TRY_PARSE('-$5,000' AS MONEY), 
TRY_PARSE('$5,000' AS MONEY)

The '$-' won't parse correctly because it is nonsense. Do a replace on that one and you should be good:

SELECT 
CASE 
  WHEN val = '$-' THEN 0 
  ELSE TRY_PARSE(val AS MONEY) 
  END AS formatted_money
dfundako
  • 8,022
  • 3
  • 18
  • 34
  • 2
    Well, one man's nonsense is another man's lazy parser corner case. `SELECT CONVERT(MONEY, '$-')` will be happily converted to `0`. (As will `'$'`, `'-'` and even the empty string.) And yes, `TRY_PARSE` and `TRY_CONVERT` use different logic -- `PARSE` uses .NET, `CONVERT` uses SQL Server's own tried and true (?) conversion rules. – Jeroen Mostert Oct 11 '19 at 13:54
  • @JeroenMostert So does my answer return the wrong result? – dfundako Oct 11 '19 at 13:56
  • Not unless your server is not using the `en-US` culture -- and it's a good idea to add `USING 'en-US'` explicitly, for just that case. (In case you're wondering, I didn't vote, nor was my comment a "your answer is wrong" comment.) – Jeroen Mostert Oct 11 '19 at 13:59
  • 1
    @JeroenMostert Converting in SQL Server is always a not-fun time. In your convert example, convert would change a blank string to 0, which as another edge case might be totally incorrect logic according to the business/developer. – dfundako Oct 11 '19 at 14:00
  • Sure. You won't find me a fan of SQL Server's own conversion rules at all, I just thought it was telling the input happened to include a case the implicit conversion actually accepts (whether or not we consider the output correct). (Per the OP, they *do* want 0 for this case, which is a lucky happenstance.) – Jeroen Mostert Oct 11 '19 at 14:01
  • Personally, I have no issue with try_convert(money,...) One could argue SELECT try_CONVERT(MONEY, '$-') returning 0.00 is perfectly valid... Plus 1 – John Cappelletti Oct 11 '19 at 14:06
  • @JohnCappelletti There are plenty of ways to skin a cat. – dfundako Oct 11 '19 at 14:10
  • Awesome I think I got it. I did try_parse as money and then casted that value as a float. Then I just updated values that were null as 0s since all of the '$-' were converted to nulls. That makes sense right? – Derek Frank Oct 11 '19 at 14:48
0

Going off your example, it might be simple to just do a replace and then cast.

Select cast(replace(replace(replace(col1,'$-','0'),'$',''),',','') as float) as NewCol from table1

I've never used try_parse, but will check it out. The above should removed the 'unwanted' and leave you with the 'good stuff'.

Jay
  • 1