I need to remove leading zeros after a decimal point

Question

this is my first time posting here and I am a basic SQL user and need help.

I have a varchar column that stores data like below:

Year.Docid
2007.000000001
2007.000000002
2007.000000003
2007.000000004
2007.000000005
2007.000000006

I need to join this data to another table that does not have all the zeros after the decimal, can someone please show me how to get the data to look like below:

Year Docid
2007.1
2007.2
2007.3
2007.4
2007.5
2007.6

I am using MICROSOFT SQL SERVER 2012

Example 1: http://stackoverflow.com/questions/10380197/sql-rounding-off-to-2-decimal-places Example 2: http://stackoverflow.com/questions/441600/write-a-number-with-two-decimal-places-sql-server — I am Cavic, Dec 13 '13 at 23:57
Please edit your question to add information, don't put it in comments. — Dour High Arch, Dec 14 '13 at 00:04

Andriy M · Accepted Answer · 2013-12-14T00:29:10.937

3

If the format is fixed, i.e. YYYY.NNNNNNNNN, you could just get the last 9 characters, convert them to int, convert the result back to varchar and concatenate back to the first 5 characters:

LEFT([Year.Docid], 5) + CAST(CAST(RIGHT([Year.Docid], 9) AS int) AS varchar(10))

However, it would make more sense to store Year and Docid as two separate int columns, in both tables. It is much easier to assemble them just for the output than do this processing every time and join on the results of it.

edited Dec 14 '13 at 00:29

answered Dec 14 '13 at 00:15

Andriy M

76,112
17
94
154

WORKED LIKE A GEM!! Thank you! – user3101154 Dec 14 '13 at 00:27

Chains · Answer 2 · 2013-12-14T00:24:48.343

0

To turn the long format into the short format:

SELECT LEFT('2007.000000001',5) + CAST(CAST(RIGHT('2007.000000001',LEN('2007.000000001')-5) AS int)AS VARCHAR)

...

To use that in a join:

SELECT
...
FROM
   TABLE_1 T1
   INNER JOIN TABLE_2 T2
   ON LEFT(T1.pk,5) + CAST(CAST(RIGHT(T1.pk,LEN(T1.pk)-5) AS int)AS VARCHAR) = T2.pk

edited Dec 14 '13 at 00:24

answered Dec 14 '13 at 00:15

Chains

12,541
8
45
62

score 0 · Answer 3 · answered Dec 14 '13 at 00:22

In SQL Server, assuming both columns are varchar, something like this should do you:

select *
from table_1 t1
join table_2 t2 on t2.docid = left(t2.docid,4)
                            + '.'
                            + convert(varchar,convert(int,right( t2.docid, len(t2.docid)-5 )))

You should bear in mind that making the one table's column an expression means that for that table/column, the query optimizer cannot use any indexes in the query plan.

score 0 · Answer 4 · answered Dec 14 '13 at 00:22

This is a bit of work, but accomplishes the task of removing the zeros from the right of the dot:

SELECT SUBSTRING(YearDocid, 0, CHARINDEX('.', yearDocId)) + 
       REPLACE(SUBSTRING(yearDocId, 
                         CHARINDEX('.', yearDocId), 
                         LEN(yearDocID)), 
               '0', '')
FROM tab1;

sqlfiddle demo

score -1 · Answer 5 · answered Jan 19 '14 at 20:05

-1

SELECT CONCAT(PARSENAME([Col_Varchar],2),'.',CONVERT(INT,PARSENAME([Col_Varchar],1))) FROM Tbl_sample

answered Jan 19 '14 at 20:05

user1059637

702
6
5

I need to remove leading zeros after a decimal point

5 Answers5

Linked