topic Re: Oracle - replace . with - in column in Operating System - HP-UX

Oracle - replace . with - in column

Ratzie — Sun, 24 Oct 2004 18:51:09 GMT

I need to update a table that in a column that anything that has a . (period) replace with - (dash)

What would be to proper sql syntax?

Update line_table
where line like '%.%'
???

Have no idea?
Thanks for the help.

Re: Oracle - replace . with - in column

Indira Aramandla — Sun, 24 Oct 2004 20:00:03 GMT

Hi,
To use special characters for column values you use the escape character. By default escape character is '\' ot you can define you own escape character.

To use the default escape character do this

SQL>set escape on

sql>update table_name
set column = '%\-% '
where column like '%\.%';

Here is an example I tested

SQL> select * from tab1;

NAME
----------
aaa.bbb
ccc.ddd
ttt.sss
SQL> update indira
set name = 'yyy\-nnn'
where name like '%\.%';

3 rows updated.

SQL> select * from indira;

NAME
----------
yyy-nnn
yyy-nnn
yyy-nnn

Note: That you can do this if all the column values are same with a '-'.

The other way is you can you your own special by

SQL>SQL> set escape '~'
Here '~' is you escape chanracter, so replace the '\' with '~'

Indira A

Re: Oracle - replace . with - in column

Hein van den Heuvel — Sun, 24 Oct 2004 20:30:45 GMT

Hmmm, . and - are not special characters in string constants I believe. And, the prior
example will replace each match with the same value for each row no?

I kinda suspect that the real problem was to replace just the dot with a dash and leave the reast of the colum as it was.

You'll need to sql/sqlplus string manipulation magic to make that happen.
Check out the 'string' functions in the SQL ref man, notably 'TRANSLATE' this time. But INSTR and SUBSTR are often useful.

http://h10025.www1.hp.com/ewfrf/wc/genericDocument?lc=en&cc=us&docname=c00223200

Check this out:

SQL> create table hein (test varchar(10));
SQL> insert into hein values ('aap.noot');
SQL> insert into hein values ('noot mies');
SQL> insert into hein values ('mies.teun');
SQL> select * from hein ;

TEST
----------
aap.noot
noot mies
mies.teun

SQL> update hein set test=translate(test,'.','-') where test like '%.%';

2 rows updated.

SQL> select * from hein ;

TEST
----------
aap-noot
noot mies
mies-teun

Of course the 'where like' clause for this example is kinda redundant in the sense that the same table results, because only dots will be replace. With the 'where', fewer updates are done.

hth,
Hein.

Re: Oracle - replace . with - in column

Yogeeraj_1 — Sun, 24 Oct 2004 22:59:53 GMT

hi,

use of "replace" and "translate" will be a better option.

Below a more complex example:
yd@MYDB.MU> variable undesirable varchar2(255)
yd@MYDB.MU> exec :undesirable := 'SAT'

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.60
yd@MYDB.MU> select username,
replace(
translate( username, :undesirable, substr(:undesirable,1,1) ),
substr(:undesirable,1,1), '' )
from all_users
where rownum < 5
/

USERNAME REPLACE(TRANSLATE(USERNAME,:UN
______________________________ ______________________________
SYS Y
SYSTEM YEM
OUTLN OULN
OPS$SWS OP$W

Elapsed: 00:00:00.01
yd@MYDB.MU>

hopw this helps!

regards
Yogeeraj

Re: Oracle - replace . with - in column

Ratzie — Mon, 25 Oct 2004 16:34:17 GMT

Thank you!