Insert decimal into varchar, but 2 pos to the left

Question

Post reply

Insert decimal into varchar, but 2 pos to the left

SkyBox

SSCarpal Tunnel

Points: 4288
More actions
May 16, 2011 at 8:07 am

#238222

My values vary from 20 to 20000, but have no decimal. I can cast as dec, but I don't need to add on anything - just insert a decimal 2 positions to the left......beginning from the right.
ie: 6288 convert to 62.88
Thanks

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

divide by 100?

_______________________________________________________________

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/

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 2

That works, but unfortunately the data is for accounting - So rounding is not an option.

Any other ideas?

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

SkyBox (5/16/2011)
That works, but unfortunately the data is for accounting - So rounding is not an option.

How does the system deal with VAT?

Is the datatype really varchar? If it is, then use REVERSE then STUFF then REVERSE.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Not sure I understand how dividing by 100 is rounding. All that does is move the decimal place two places to the left.

_______________________________________________________________

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/

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

--==First, lets knock up some sample data==--

DECLARE @TABLE AS TABLE(number VARCHAR(50))

INSERT INTO @TABLE (number)

SELECT TOP 250000

CONVERT(VARCHAR(50),FLOOR(RAND(CHECKSUM(NEWID())) * 20000)+1)

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

--==Query==-

SELECT number AS original,

CONVERT(MONEY,number) / 100 AS option_1,

CASE WHEN LEN(number)-2 > 0

THEN LEFT(number,LEN(number)-2)+'.'+RIGHT(number,2)

ELSE LEFT(number,2)+'.'+RIGHT(number,2) END AS option_2

FROM @TABLE

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 6

Sean Lange (5/16/2011)
Not sure I understand how dividing by 100 is rounding. All that does is move the decimal place two places to the left.

To use the div/100, I must need to insert a decimal first - because it drops off the change.

,TRANS_AMOUNT/100 as TransAmt

ie: 6288 becomes 62

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

ahh gotcha it is doing int division. Since you original values do not have a decimal place they will be interpreted as an int. You need to force it use decimal division like this.

TRANS_AMOUNT/100. as TransAmt

Notice I just added the decimal after the 100 which is the same as 100.0

_______________________________________________________________

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/

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 8

Sean Lange (5/16/2011)
ahh gotcha it is doing int division. Since you original values do not have a decimal place they will be interpreted as an int. You need to force it use decimal division like this.
TRANS_AMOUNT/100. as TransAmt
Notice I just added the decimal after the 100 which is the same as 100.0

This worked, but your suggestion is cleaner.

,CAST(TRANS_AMOUNT AS DEC(12,2))/100 AS TransAmt

Thank you all!