CODEWITHSUNDEEP
sqlsql-server
J.Ngo
J.Ngo

Reputation: 31

Adding trailing and leading zeroes

How do we convert and add trailing and leading zeros to a number? For example 123.45. I need to make this ten digits long and have padding numbers in front and back. I would like to convert it to 0001234500. Two trailing numbers after the last digit of the decimal. Remove the decimal. Fill in the remaining space with zeroes for the leading end.

I have this so far and it adds trailing zeroes and removes the decimal.

REPLACE(RIGHT('0'+CAST(rtrim(convert(char(10),convert(decimal(10,4),Field))) AS VARCHAR(10)),10),'.','') as New_Field

Upvotes: 2

Views: 16818

Answers (2)

wiesion
wiesion

Reputation: 2465

In MySQL you would have RPAD and LPAD to get stuff like this done, in SQL Server (2012+) you can get something similar by working with FORMAT.

Easiest way is to FORMAT your numbers with a dot so that they take the right place in the format string, then remove that dot. You need to specify a locale, since in different regions you will get a different decimal sign (even if you use . within the format pattern, you would get , in various locales) - using en-US makes sure you get a dot.

REPLACE(FORMAT(somenumber, '000000.0000', 'en-US'), '.', '')

A few examples:

WITH TempTable(somenumber) AS (
    SELECT 3
    UNION SELECT 3.4 
    UNION SELECT 3.45 
    UNION SELECT 23.45 
    UNION SELECT 123.45
)
SELECT 
    somenumber,
    REPLACE(FORMAT(somenumber, '000000.0000', 'en-US'), '.', '')
FROM 
    TempTable;

Gives

3.00    0000030000
3.40    0000034000
3.45    0000034500
23.45   0000234500
123.45  0001234500

Upvotes: 3

Thom A
Thom A

Reputation: 96055

You seem to really be overthinking what you need to do here. If we take it in steps, perhaps you'll see that this can be achieved much more easily. This solution runs under the idea that the value 123.45 becomes 0001234500 and 6.5 becomes 0000065000.

Firstly, let's pad out the right hand side of the number 123.45 so that we have 1234500 That's easy enough : 123. 45 * 100 = 12345 So, to get 1234500 we simply need to multiple it by a couple of extra factors of 10:

SELECT 123.45 * 10000; --1234500.00

Ok, now, let's get rid of those decimal places. Easiest way, convert it to an int:

SELECT CONVERT(int, 123.45 * 10000); --1234500

Nice! Now, the finalstep, the leading 0's. A numerical value, in SQL Server, won't display leading zeros. SELECT 01, 001.00; Will return 1 and 1.00 respectively. A varchar however, will though (as it's not a number). We can, therefore, make use of that with a further conversion, and then then use of RIGHT:

SELECT RIGHT('0000000000' + CONVERT(varchar(10),CONVERT(int,123.45 * 10000)),10);

Now you have the value you want '0001234500'.

If you're only after padding, (so 6.5 becomes 0006500) then you should be able to work out how to achieve this with the help above (hint you don't need RIGHT).

Any questions, please do ask.

Upvotes: 2

Related Questions