Post reply

inserting decimal point

  • February 9, 2006 at 7:55 am

    #110971

    I have a field that is varchar(10).  I need to insert a decimal point after the third character.  In some cases, there is only 3 characters, in others up to 5.  It is not all numeric, in some instances there is a letter in the front.  (ICD9 codes for those clinical out there.).

    Thanks!

  • February 9, 2006 at 8:03 am

    #619797

    Can you provide some code examples of the different variants, and an explanation on how each should be treated?

    /Kenneth

  • February 9, 2006 at 8:08 am

    #619802

    Sure...

    The field may have the following values:

    250

    25001

    7265

    V4521

    And I need them to be

    250.

    250.01

    726.5

    V45.21

    Does that help?

  • February 9, 2006 at 8:12 am

    #619807

    What about

    UPDATE tblBlah

    SET MyField = LEFT(LTRIM(MyField),3) + '.' + SUBSTRING(LTRIM(MyField),4,6)

    I would run it as a select first to see if it gives you what you want.

  • February 9, 2006 at 8:53 am

    #619820

    Heh, thought it would be a catch somewhere with all the different lenghts and such

    Allen got it right on, substringing and concatenating the '.' in between is the way to go.

    /Kenneth

     

  • February 9, 2006 at 9:54 am

    #619844

    It worked.

    But.... for let's say 250, I realize I need it to be 250.00 (it currently is 250.   just like I asked for!)

    ALL others worked great! 

    Can I do this in another statement?

  • February 9, 2006 at 11:36 am

    #619880

    declare @table table

    (

    some_value varchar(10)

    )

    insert @table

    select '250'

    union all

    select '25001'

    union all

    select '7265'

    union all

    select 'V4521'

    update @table

    set some_value = x.some_value

    from @table t

    join

    (

    select some_value old_value,case when LEN(some_value) <= 3 then LEFT(LTRIM(some_value),3) + '.00'

    when LEN(some_value) <= 4 then LEFT(LTRIM(some_value),3) + '.' + SUBSTRING(LTRIM(some_value),4,len(some_value))+ '0'

    else LEFT(LTRIM(some_value),3) + '.' + SUBSTRING(LTRIM(some_value),4,len(some_value))

    end as some_value

    from @table

    )x

    on x.old_value = t.some_value

    select * from @table

    Mathew J Kulangara
    sqladventures.blogspot.com

  • February 10, 2006 at 3:10 am

    #620052

    What about 726.5 then? Is it good as is, or should that be 726.50 also..? (since you wanted 2 decimals on 250.00)

    /Kenneth

  • February 10, 2006 at 3:12 am

    #620053

    It's always the spec that gets us!

  • February 10, 2006 at 3:16 am

    #620057

    Ah, just realized that Mathew's solution did add a zero to make up 726.50.

    /Kenneth

  • February 10, 2006 at 3:32 am

    #620060

    Using Mathew's table definition (and only because I like to be different ):

    select some_value, substring(stuff(some_value + '00', 4, 0, '.'), 1, 6) from @table

  • February 10, 2006 at 3:37 am

    #620061

    Or

    select some_value, left(some_value, 3) + '.' + substring(some_value + '00', 4, 2) from @table

  • February 10, 2006 at 5:27 am

    #620075

    Ah.... how true, it's always the spec!

    It actually turned out that the three character values should get NO update (ok people, make up your minds!!).  Here's what worked:

    UPDATE tablename

    SET field = LEFT(LTRIM(field),3) + '.' + SUBSTRING(LTRIM(field),4,6) where LEN(field) > 3

    Thanks so much for all the help!!!!

  • February 10, 2006 at 5:36 am

    #620077

    This might be slightly simpler:

    UPDATE tablename SET field = stuff(field, 4, 0, '.') where LEN(field) > 3

  • February 10, 2006 at 7:02 am

    #620099

    How about this

    STUFF(col+SUBSTRING('0',SIGN(LEN(col) % 4)+1,1)+REPLICATE(SUBSTRING('0',SIGN(LEN(col) % 3)+1,1),2),4,0,'.')

    Far away is close at hand in the images of elsewhere.
    Anon.

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

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