Post reply

small help with a column value....plz urgent

  • April 3, 2008 at 7:22 am

    #120907

    i hada a column abc with a value 123-345-4567 so i want to change it to 1233454567 so can i know how can i do that in ssis or ssms anyway plz let me know urhgent ...

    or

    the real purpose of me changing that is i jus want the column to be compared to same column in other table which is in 1233454567 format so when i make a join it doesnt return any data for me but i know there are many rows which matches so is there any other alternative way plz uregnt let me know....

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • April 3, 2008 at 7:31 am

    #797797

    If you want to change the data:

    update dbo.YourTableName

    set YourColumnName = replace(YourColumnName, '-', '')

    where YourColumnName like '%-%'

    That'll be better than having a function in your Join clause, in terms of performance. If updating the data will mess something up, then use the "replace()" function in the join, but expect it to be slow.

    - 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

  • April 3, 2008 at 7:42 am

    #797810

    if i dont want to change the data can i use the same replace in a join condition like

    on d.abc= c.replace('abc'----i could not get that could you please let me know ..beacuse i dont want to change the data ..thkz in Advance

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • April 3, 2008 at 8:33 am

    #797856

    d.col1 = replace(c.col1, '-','')

    - 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

  • April 3, 2008 at 10:25 am

    #797940

    Keep in mind that doing a replace in a join will cause a table scan.

  • April 3, 2008 at 10:36 am

    #797946

    yeah may be tats the reason its been taking long time i didnt know that thanks micheal so i belive it better to update the data than using replace in the join condition.

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

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

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