May 16, 2017 at 1:24 pm
So, i'm currently using the below to change a VARCHAR field over to a INT and remove the first character (contains a £).
The goal; enable maths on the field. I realise long term i will want to change the field to be an INT and deal with the £ on the application side but is my code scalable and best practice.
CONVERT(INT,RIGHT(RTRIM(qb.Amount), LEN(qb.Amount) - 1)),
Cheers
T
May 16, 2017 at 1:29 pm
Tennim - Tuesday, May 16, 2017 1:24 PMSo, i'm currently using the below to change a VARCHAR field over to a INT and remove the first character (contains a £).The goal; enable maths on the field. I realise long term i will want to change the field to be an INT and deal with the £ on the application side but is my code scalable and best practice.
CONVERT(INT,RIGHT(RTRIM(qb.Amount), LEN(qb.Amount) - 1)),Cheers
T
The bad practice is on the data definition. You could simplify by keeping everything as money, which would take currency signs.
DECLARE @Sample TABLE(
Amount varchar(20));
INSERT INTO @Sample
VALUES('£1543'), ('£35413.33'), ('5611'), ('35413.55');
SELECT qb.Amount, CONVERT( int, CONVERT(money, Amount))
FROM @Sample qb
May 16, 2017 at 1:44 pm
Cheers Luis,
Completly agree that the data definition is the root of my problems.
I've tested your snippet at home, will try in work tomorrow with test DB.
Question for you, based on the results... Is there implicit conversion between VARCHAR and Money?
T
May 16, 2017 at 1:51 pm
Tennim - Tuesday, May 16, 2017 1:44 PMCheers Luis,Completly agree that the data definition is the root of my problems.
I've tested your snippet at home, will try in work tomorrow with test DB.
Question for you, based on the results... Is there implicit conversion between VARCHAR and Money?
T
If I understand your question correctly. Varchar can be implicitly converted to money. Money has higher precedence, so when mixing datatypes, everything will be converted to money.
In this case, however, the conversion is explicit.
May 16, 2017 at 4:17 pm
But isn't the MONEY type a float? If so, you will have to be careful when multiplying/dividing against it. Right Luis? I got bitten by that once many moons ago and vowed never to use floats again.
May 17, 2017 at 7:27 am
autoexcrement - Tuesday, May 16, 2017 4:17 PMBut isn't the MONEY type a float? If so, you will have to be careful when multiplying/dividing against it. Right Luis? I got bitten by that once many moons ago and vowed never to use floats again.
That's right. I had a hard time trying to match a calculation that used an average on a money column, until I found out that the data type was the one to blame.
May 17, 2017 at 10:57 am
Luis Cazares - Wednesday, May 17, 2017 7:27 AMautoexcrement - Tuesday, May 16, 2017 4:17 PMBut isn't the MONEY type a float? If so, you will have to be careful when multiplying/dividing against it. Right Luis? I got bitten by that once many moons ago and vowed never to use floats again.That's right. I had a hard time trying to match a calculation that used an average on a money column, until I found out that the data type was the one to blame.
Are you sure about that? I don't money is a real/float. But SQL is notoriously inaccurate in certain decimal place calcs, maybe that was the issue with avg, etc.?
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".
May 17, 2017 at 12:31 pm
ScottPletcher - Wednesday, May 17, 2017 10:57 AMLuis Cazares - Wednesday, May 17, 2017 7:27 AMautoexcrement - Tuesday, May 16, 2017 4:17 PMBut isn't the MONEY type a float? If so, you will have to be careful when multiplying/dividing against it. Right Luis? I got bitten by that once many moons ago and vowed never to use floats again.That's right. I had a hard time trying to match a calculation that used an average on a money column, until I found out that the data type was the one to blame.
Are you sure about that? I don't money is a real/float. But SQL is notoriously inaccurate in certain decimal place calcs, maybe that was the issue with avg, etc.?
Silly me. I did have the problem caused by the money data type. However, it's actually an "exact numeric" data type, not an "approximate numeric" data type (like float). The problem comes with the scale, which is limited to 4.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply