[Best Practice] VARCHAR >> INT and Truncation

Question

[Best Practice] VARCHAR >> INT and Truncation

Tennim

SSC Enthusiast

Points: 122
More actions
May 16, 2017 at 1:24 pm

#330350

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

Viewing 8 posts - 1 through 7 (of 7 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Tennim - Tuesday, May 16, 2017 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

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Tennim SSC Enthusiast Points: 122 More actions · Answer 2

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Tennim - Tuesday, May 16, 2017 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

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

autoexcrement SSCertifiable Points: 5951 More actions · Answer 4

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.

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

autoexcrement - Tuesday, May 16, 2017 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.

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 100947 More actions · Answer 6

Luis Cazares - Wednesday, May 17, 2017 7:27 AM
autoexcrement - Tuesday, May 16, 2017 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.
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) A socialist is someone who will give you the shirt off *someone else's* back.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

ScottPletcher - Wednesday, May 17, 2017 10:57 AM
Luis Cazares - Wednesday, May 17, 2017 7:27 AM
autoexcrement - Tuesday, May 16, 2017 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.
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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2