CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
user3101154
user3101154

Reputation: 23

I need to remove leading zeros after a decimal point

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

Upvotes: 2

Views: 1512

Answers (5)

user1059637
user1059637

Reputation: 712

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

Upvotes: -1

Andriy M
Andriy M

Reputation: 77677

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.

Upvotes: 3

Chains
Chains

Reputation: 13157

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

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

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

Upvotes: 0

Nicholas Carey
Nicholas Carey

Reputation: 74267

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.

Upvotes: 0

Related Questions