Conversion of currency stored as varchar to decimal

Question

Conversion of currency stored as varchar to decimal

masterelaichi

SSC-Addicted

Points: 444
More actions
September 20, 2022 at 5:43 am

#4089643

Hi all,
I am trying to convert currency values stored as a string to decimal. In my dataset, there is a currency column with values like $15,000.50 stored as varchar. I am trying to convert it to 15000.50
I tried SELECT CONVERT (decimal , '$15,000.50 ') but end up with the dreaded conversion type error. In another scenario I tried to replace "$" with a blank like so and it worked
select CONVERT(decimal (10,2),replace('$50.50','$',''))
This wouldn't work in my first example because of the $ and comma in the value. Is there a method to do this more efficiently ?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

It's always lovely to have numbers, dates , datetime info stored in (N)varchar as it guarantees for problems in the future.

Is there any chance you can just replace the '$' with a blank in the varchar kolom, without trying to convert to number?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

masterelaichi SSC-Addicted Points: 444 More actions · Answer 2

I did it using a double replace. But wondering if there is a cleaner way to do it

select TRY_CONVERT(decimal (10,2),REPLACE(REPLACE('$16,500.50','$',''),',','') )

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

assuming you are using US formats you can do

select convert(decimal(10, 2), parse('$16,500.50' as money using 'en-US'))

masterelaichi SSC-Addicted Points: 444 More actions · Answer 4

frederico_fonseca wrote:

assuming you are using US formats you can do
select convert(decimal(10, 2), parse('$16,500.50' as money using 'en-US'))

thanks this worked. I tweaked it by using try_parse as there are non-numeric values in my column

This reply was modified 2 years, 2 months ago by masterelaichi.

arvind.patil 98284 Mr or Mrs. 500 Points: 510 More actions · Answer 5

you can convert it money data type

SELECT CONVERT (money , '$15,000.50 ')

ScottPletcher SSC Guru Points: 101096 More actions · Answer 6

SELECT CONVERT (decimal(9, 2) , CAST('$15,000.50' AS money))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 7

NO! the old Sybase MONEY datatypes have ugly rounding problems; google it! https://www.red-gate.com/hub/product-learning/sql-prompt/avoid-use-money-smallmoney-datatypes

Please post DDL and follow ANSI/ISO standards when asking for help.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 8

Follow Brent O's rule; " Store data how it is used (NOT displayed) and use it how it is stored!'

Please post DDL and follow ANSI/ISO standards when asking for help.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 9

Not really. A basic principle of a modern tiered architecture is xto do diplay owrk in a presentation layer, in the database.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

jcelko212 32090 wrote:

NO! the old Sybase MONEY datatypes have ugly rounding problems; google it! https://www.red-gate.com/hub/product-learning/sql-prompt/avoid-use-money-smallmoney-datatypes%5B/quote%5D

Agreed, but that's now how the MONEY datatype is being used in this instance. Instead, it's being used to short circuit currency symbols and thousands separators in a VERY efficient fashion. There's nothing wrong with its "interim usage", as several of the posters did on this thread. Even an implicit conversion is ok on the way into a target table that correctly uses Numeric or Decimal.

Also, if you read up on those two datatypes, you'll find they also have some rather nasty rounding problems in certain common cases especially when it comes to things like compound interest on decreasing principal balances.

You've also responded to 3 posts above and we have no clue which posts you're addressing. It makes even well-founded, correct answers fairly useless.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)