Re: help on manipulating a string !!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 20 May 1999 12:06:28 GMT
Message-ID: <3744f995.3926115@newshost.us.oracle.com>

A copy of this was sent to "deb" <dnanda_at_netcom.ca> (if that email address didn't require changing) On Wed, 19 May 1999 20:46:24 -0400, you wrote:

>I want to create a procedure which takes a special string as an input.Please
>note that the input string may have an apostrophe (').If there is an
>apostrophe in the string then it will insert another one immediately
>following the previous one... for example
>
>the string ('mike o'grady') should be converted to ('mike o''grady')...
>

replace( your_string, '''', '''''' )

is the function you want. consider:

SQL> variable x varchar2(25)
SQL> exec :x := 'mike o''grady';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> select :x from dual;

:X

---

mike o'grady

SQL> select replace( :x, '''', '''''' ) from dual;

REPLACE(:X,'''','''''')

---

mike o''grady

>1) Another thing that I wanted to know if oracle can accept special
>characters like '&' and ' in a string.
>

For the & -- that is a sqlplus'ism. simply issue:

SQL> set define off

and the & will not be considered a special character. For the ', if you use character string constants, then they need to be doubled up. If you use bind variables -- they don't. Consider this pro*c snippet that shows what I mean:

static void process()
{
exec sql begin declare section;
varchar my_string[100];
exec sql end declare section;

    strcpy( my_string.arr, "mike o'grady" );     my_string.len = strlen(my_string.arr);

    exec sql whenever sqlerror do sqlerror_hard();     exec sql insert into t values ( 'mike o''grady' );     exec sql insert into t values ( :my_string );     exec sql commit work;
}

SQL> select * from t;

X

---

mike o'grady
mike o'grady

so, one insert uses character string constants which must double up the quotes. the other uses bind variables -- it does not. bind variables will make the whole thing go faster as well...

>thanks /dip
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu May 20 1999 - 07:06:28 CDT