Remove two sets of leading zeros from SQL

Question

I have a set of data that looks like this;

DOCUMENT_ID
===
019239.03491
019239.03497
019239.03498
019239.03500
019239.03501
019239.03503
019239.03514
019239.03521
019168.00845
019168.00846
019168.00847
019168.00848
019168.00850
019168.00860
019168.00861
019168.00866
019168.00867
019168.00868
019168.00869
019168.00870

I need to remove leading zeros from the left most part of the field and also the leading zeros after the decimal point. Please note that there could be more than one leading zero.I other words I need the data above to look like the data below;

Any information or assistance would help. I am using MS SQL SERVER 2012.

possible duplicate of [I need to remove leading zeros after a decimal point](http://stackoverflow.com/questions/20577995/i-need-to-remove-leading-zeros-after-a-decimal-point) — Data Masseur, May 06 '14 at 18:12
@JohnHartsock - thanks for correction, had wrong language on brain. — outis nihil, May 06 '14 at 19:12

score 1 · Answer 1 · answered May 06 '14 at 22:33

Double Casts do the job rather well.


    CREATE TABLE DOCS( DOCUMENT_ID VARCHAR(13) );

    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019239.03491');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019239.03497');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019239.03498');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019239.03500');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019239.03501');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019239.03503');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019239.03514');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019239.03521');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00845');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00846');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00847');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00848');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00850');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00860');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00861');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00866');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00867');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00868');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00869');
    INSERT INTO DOCS (DOCUMENT_ID) VALUES ('019168.00870');


    SELECT 
      CAST(CAST(LEFT(DOCUMENT_ID,CHARINDEX('.',DOCUMENT_ID)-1) AS INT) AS VARCHAR(13))
      + '.' +
      CAST(CAST(RIGHT(DOCUMENT_ID,CHARINDEX('.',REVERSE(DOCUMENT_ID))-1) AS INT) AS VARCHAR(13))
      AS NEW_DOCUMENT_ID

    FROM DOCS

Results:

NEW_DOCUMENT_ID
19239.3491
19239.3497
19239.3498
19239.3500
19239.3501
19239.3503
19239.3514
19239.3521
19168.845
19168.846
19168.847
19168.848
19168.850
19168.860
19168.861
19168.866
19168.867
19168.868
19168.869
19168.870

Check a SQLFiddle.

score 0 · Answer 2 · answered May 06 '14 at 18:14

Test Data

DECLARE @TABLE TABLE(Doc_ID VARCHAR(20))
INSERT INTO @TABLE VALUES 
('019168.00861'),
('019168.00866'),
('019168.00867'),
('019168.00868'),
('019168.00869'),
('019168.00870'),
('19168.870')

Query

SELECT CASE WHEN LEFT(Doc_ID, 1) = '0' 
               THEN SUBSTRING(Doc_ID, 2, CHARINDEX('.',Doc_ID)-1 ) 
            ELSE SUBSTRING(Doc_ID, 1, CHARINDEX('.',Doc_ID)) END 
        + CASE WHEN LEFT(SUBSTRING(Doc_ID, CHARINDEX('.',Doc_ID)+ 1, LEN(Doc_ID)), 1) = '0'
           THEN SUBSTRING(Doc_ID, CHARINDEX('.',Doc_ID)+ 2, LEN(Doc_ID))
            ELSE SUBSTRING(Doc_ID, CHARINDEX('.',Doc_ID)+ 1, LEN(Doc_ID))
            END 
                AS FinalValue
FROM @TABLE

Result Set

╔════════════╗
║ FinalValue ║
╠════════════╣
║ 19168.0861 ║
║ 19168.0866 ║
║ 19168.0867 ║
║ 19168.0868 ║
║ 19168.0869 ║
║ 19168.0870 ║
║ 19168.870  ║
╚════════════╝

Remove two sets of leading zeros from SQL

2 Answers2