Post reply

getting rid of 0's in the start of a varchar(6) column

  • November 18, 2008 at 8:55 am

    #124948

    Hi All,

    I am facing a problem in avoiding trailing zeros from my stage source…

    Actually I have 3 columns which require to be looked up from an emp code from employee dimension. But the stage source that I am getting has codes starting with zeros…

    Like 006708 (varchar(6)

    But in emp dimension it is present as 6708 (varchar(6)

    Some are like A09876 (so some sarts with char too…

    Now I want to split them getting from the source and also want to get rid of zeros in the start ?

    Any help on this?

    Thanks in advance for your help.

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • November 18, 2008 at 1:01 pm

    #900151

    Hi,

    You should be using derived column transformation, and use substring or available string functions. This would help.

    Raj

  • November 18, 2008 at 8:35 pm

    #900270

    Rajesh (11/18/2008)

    Hi,

    You should be using derived column transformation, and use substring or available string functions. This would help.

    Raj

    Easy enough to say... what formula would you use to return the following in the format requested by the OP?

    126708

    A26708

    AB6708

    026708

    006708

    A06708

    AB6708

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 19, 2008 at 7:44 am

    #900525

    I am sorry, little confused with the reply. Could you be specific, what's the input and what needs to be the op with few examples. If the the volume of data is not large enough you could use cursors or case conditions in select statement.

    Cheers,

    Raj

  • November 19, 2008 at 9:47 am

    #900616

    Hi,

    How about a function that strips the Alpha out like this

    CREATE FUNCTION dbo.RemoveAlpha(@Str varchar(1000))

    RETURNS VARCHAR(1000)

    BEGIN

    declare @NewStr varchar(1000),

    @i int

    set @i = 1

    set @NewStr = ''

    while @i <= len(@str)

    -- STRIP OUT ALPHA CHARS

    begin

    if substring(@str,@i,1) like '%[0-9]%'

    begin

    set @NewStr = @NewStr + substring(@str,@i,1)

    end

    else

    begin

    set @NewStr = @NewStr

    end

    set @i = @i + 1

    end

    RETURN Rtrim(Ltrim(@NewStr))

    END

    Then get the data using the function whilst weeding out the 0's

    SELECT REPLACE(LTRIM(REPLACE(([dbo].[RemoveAlpha](SomeEmployeeNumColumn)) , '0', ' ')), ' ', '0')

    FROM EmployeesTable

    Test ok on my box as

    126708

    A26708

    AB6708

    026708

    006708

    A06708

    AB6708

    Becomes

    126708

    26708

    6708

    26708

    6708

    6708

    6708

  • November 19, 2008 at 12:41 pm

    #900695

    I don't want to remove characters from the start....

    if it starts with a char leave it as it is....

    Only if it starts with 0 trim the zeros

    Thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • November 19, 2008 at 12:43 pm

    #900696

    Well the input is like this:

    Test ok on my box as

    006708

    A26708

    CB6708

    026708

    006708

    K06708

    AB6708

    And the output should be like this:

    6708

    A26708

    CB6708

    26708

    6708

    K06708

    AB6708

    Hope it's clear?

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • November 19, 2008 at 7:58 pm

    #900831

    Rajesh (11/19/2008)

    I am sorry, little confused with the reply. Could you be specific, what's the input and what needs to be the op with few examples. If the the volume of data is not large enough you could use cursors or case conditions in select statement.

    Cheers,

    Raj

    I was suggesting that you actually offer a code solution to the original poster. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 19, 2008 at 8:13 pm

    #900837

    If it's numeric, cast to integer, otherwise leave as is:

    declare @value varchar(10)

    Set @Value = '0000006708'

    if IsNumeric(@value) = 1

    select cast(@value as integer)

    else

    select @value

    Phil

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 19, 2008 at 9:36 pm

    #900863

    Phil Parkin (11/19/2008)

    If it's numeric, cast to integer, otherwise leave as is:

    declare @value varchar(10)

    Set @Value = '0000006708'

    if IsNumeric(@value) = 1

    select cast(@value as integer)

    else

    select @value

    Phil

    Works fine... but OP also wants to know what to do with things like A06708.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 21, 2008 at 2:51 pm

    #901894

    Thanks Phil..It works now

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • November 23, 2008 at 5:41 pm

    #902235

    Ria (11/21/2008)

    Thanks Phil..It works now

    Umm... what about the things that start with "A"?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 12 posts - 1 through 11 (of 11 total)

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