Replace leading zeros

  • I have the following data with a data type of varchar(14)

    05380101010000

    05380101020000

    05380101101000

    05380011103000

    I need to to get rid of the leading zeros after I substring the numbers I want out.

    Results:

    101

    102

    1101

    1103

  • Can you cast/convert them into a numeric data type (int, for example)? That'll get rid of leading zeroes.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Leading zeros or trailling zeros? looking at the data, I'm thinking trailing.

  • In this case I have both.

  • Show how you are pulling out the values from the initial values.

  • I am pulling them like this:

    SUBSTRING(REPLACE(LTRIM(REPLACE(col1, '0', ' ')), ' ', '0'), 7, 3)

    This works for the the first two numbers but not for the last two.

    Outputs the following:

    101

    102

    101 but needs to be 1101

  • I'm confused. Hightlight (BOLD) the values you are trying to extract:

    05380101010000

    05380101020000

    05380101101000

    05380011103000

  • 05380101010000

    05380101020000

    05380101101000

    05380011103000

  • I don't see where leading zeroes have anything to do with that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Try this:

    select reverse(cast(cast(reverse(substring(col1,8,4)) as int) as varchar(4)))

  • This should get you what you need. Just put in your column name where I have the string literal.

    select reverse(cast(cast(reverse(substring('05380101010000', 8, 4)) as int) as varchar(4)))

    Edit: And, of course, we both post the solution at the same time. 🙂

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Not only that, but that is what I thought was needed from the start, but I got confused by the subsequent posts until I had the OP show what was needed.

  • Yeah, I took it at face value. I still don't understand what this has to do with leading zeroes, like the title.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Seems an odd requirement, but this is an alternative:

    SELECT SUBSTRING(Field1, 8, 8 - PATINDEX('%[1-9]%', REVERSE(Field1)))

    FROM (

    SELECT '05380101010000' AS Field1 UNION ALL

    SELECT '05380101020000' UNION ALL

    SELECT '05380101101000' UNION ALL

    SELECT '05380011103000'

    ) TestData

  • Sorry about the title...but this is the correct way to get what I wanted. Your way still left off the end numbers.

    SUBSTRING(col,PATINDEX('%[^0]%',col),10)

Viewing 15 posts - 1 through 15 (of 19 total)

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