Post reply

Selecting Specific Data from a Column

  • December 27, 2002 at 7:47 am

    #80562

    I have a column named "ship_to_addr_3." In this column there is city, state, and zip combined (i.e. New York, NY 10018). I want to remove only this city so that the column would like like this: New York.

    How can I do this? I think a combination of len or replace, etc. would work, but I'm not sure how to go about it.

    Thanks for the help.

  • December 27, 2002 at 8:12 am

    #445184

    This will work:

    Select substring(ship_to_addr_3, 1, CHARINDEX(',', ship_to_addr_3,1)-1)

    from tablename

  • December 27, 2002 at 8:32 am

    #445186

    Thanks a lot. This didn't work at first becase I had a few columns that didn't have a "," so it was returning -1 as part of the substring expressions and it was erroring. However, I created a case statement that fixed this and now I am all set.

    Thanks again and Happy Holidays.

Viewing 3 posts - 1 through 2 (of 2 total)

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