Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2005
    Posts
    5

    Unanswered: Oracle 9i SQL String Manipulation Question

    Hello.

    I'd appreciate some help with some Oracle 9i SQL.

    The following is an example of the SQL Query I currently have:

    SELECT customer_name FROM orders where oid = 1594

    The resulting row looks like:

    Jordan|s Grocery

    That is an escape character that is contained in the field in lieu of an apostrophe for Jordan's Grocery.

    I need to modify the above SQL query to search for the escape character "\\|" and replace it with "'" (a single apostrophe).

    Thanks for your help.

    Earl

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    The REPLACE function should help you.

    HTH & Regards,

    RBARAER
    Last edited by RBARAER; 12-06-05 at 13:41. Reason: Link to 9iR2 doc instead of 10g doc
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by EarlB
    Hello.

    I'd appreciate some help with some Oracle 9i SQL.

    The following is an example of the SQL Query I currently have:

    SELECT customer_name FROM orders where oid = 1594

    The resulting row looks like:

    Jordan|s Grocery

    That is an escape character that is contained in the field in lieu of an apostrophe for Jordan's Grocery.

    I need to modify the above SQL query to search for the escape character "\\|" and replace it with "'" (a single apostrophe).

    Thanks for your help.

    Earl
    TO REPAIR ALL RECORDS.

    update orders
    set customer_name = replace(customer_name,'|','''')
    where instr(customer_name,'|') > 0;

    TO REPAIR ONE A SINGLE RECORD.

    update orders
    set customer_name = replace(customer_name,'|','''')
    where oid = 1594;
    Last edited by beilstwh; 12-06-05 at 17:03.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2005
    Posts
    5

    Thanks.

    I must have not been clear. I'm needing to change the results for the select query only, replacing the escape character with the apostrophe. I do NOT want to change the database. It utilizes the escape character on purpose as apostrophe's cause the web app to go to hell in a hand basket. The web app utilizes java methods to insert and remove the escape character but I need to do this with my SQL query for another purpose.

    Thanks,
    Earl

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It should be plain from the examples above.


    SELECT replace(customer_name,'|','''') customer_name
    FROM orders
    where oid = 1594;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jul 2005
    Posts
    5
    It wasn't before but it is now. Thanks, Earl

    Quote Originally Posted by beilstwh
    It should be plain from the examples above.


    SELECT replace(customer_name,'|','''') customer_name
    FROM orders
    where oid = 1594;

Posting Permissions

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