Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    29

    Unanswered: Replacing the First Character alone in a Column

    Hi,
    I want to update the Values of a Column in one of the tables with a different value. I have table A which has two fields Loc_Id and Location. In this table we have geocoded the Local area map where our Site is located originating from one end of the city. Now the I want to reverse the whole Geocoding as our site is moving to the other end of the city which is exactly in the opposite direction on the other side of the map. So for my application to work I need to reverse the whole geocoding. Say the location id (Loc_Id) was A01ANSU, I want it replace as Z01ANSU. I used the replace function to do the same. But the problem is where ever in the particular entry it enclounters A it replaces with Z. Is there any way I can replace the first character alone. Thanks in advance
    Regards
    Dinesh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Replacing the First Character alone in a Column

    You can do this:

    UPDATE table
    SET col = TRANSLATE( SUBSTR(col,1,1), 'ABC...Z', 'ZYX...A' ) || SUBSTR(col,2);

    (replace the ... by the full set of codes!)

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tony, that's a very nice technique

    what happens if the first character is not a letter?

    in this situation, if every first character is A-Z, it's not a problem, but generally speaking it's a good idea always to have a WHERE clause in any UPDATE statement where the change is conditional


    rudy

  4. #4
    Join Date
    May 2002
    Posts
    29
    Andrewst & R937
    Both ur inputs where really helpful and it worked fine. Thanks for helping me solve my problem.
    Thanks once again with regards
    Dinesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •