Post reply

PLEASE HELP ****** Stripping #s from one column to update 2 columns *******

  • September 27, 2016 at 2:19 pm

    #321699

    Hi-

    Can you please help me figure out why my results are duplicating. Original field looks like this

    7 1601081408920063

    I would like to take 7 and update another field with it, Than I would like to take 1601081408920063 and update the Original field with it so it will no longer contain the 7.

    select left (Original,1) as FC,

    right(Original,20) as OrigNum

    into #temp1

    from Numbers_Table cm

    where cm.[status] <>'9'

    and (cm.Original like'6%' or cm.Original like '7%' or cm.Original like '8%' )

    select * from #temp1

    FCOrigNum

    71601081408920063

    71601081408920063

    71507210892006300

    71507210892006300

  • September 27, 2016 at 3:08 pm

    #1903486

    declare @mytable table (FC int, OrigNum varchar(50))

    insert into @mytable (OrigNum)

    values

    ('7 1601081408920063')

    ,('7 1601081408920063')

    ,('7 1507210892006300')

    ,('7 1507210892006300')

    update @mytable

    set FC = left(OrigNum,1)

    ,Orignum = right(OrigNum, len(OrigNum)-2)

    select * from @mytable

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • September 27, 2016 at 3:15 pm

    #1903489

    Without seeing the layout of your numbers_table table (which is redundundant by the way) and some sample data, we have no way of telling why you're seeing duplicates in your output. Please look HERE[/url] for how to set up a problem so people can help you with coded and tested solutions.

    Also, tables in databases have columns, not fields. 😉

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • September 27, 2016 at 3:25 pm

    #1903495

    Thank you I will give this a try 🙂

  • September 27, 2016 at 3:29 pm

    #1903497

    I just sent you an update query. It's not going to solve your problem of duplicate data in the table.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • September 27, 2016 at 5:00 pm

    #1903521

    The Dixie Flatline (9/27/2016)

    Without seeing the layout of your numbers_table table (which is redundundant by the way) ...

    Made me laugh. Hope it was deliberibiberate 🙂

    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.

  • September 27, 2016 at 6:10 pm

    #1903529

    I have no idea what you're trying to do but it seams simple. Please post your desired results. On a side note -

    This:

    and (cm.Original like'6%' or cm.Original like '7%' or cm.Original like '8%' )

    ..could be replaced with:

    and (cm.Original like '[6-8]%')

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 7 posts - 1 through 6 (of 6 total)

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