- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Oracle - replace . with - in column
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-24-2004 11:51 AM
тАО10-24-2004 11:51 AM
What would be to proper sql syntax?
Update line_table
where line like '%.%'
???
Have no idea?
Thanks for the help.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-24-2004 01:00 PM
тАО10-24-2004 01:00 PM
Re: Oracle - replace . with - in column
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-24-2004 01:30 PM
тАО10-24-2004 01:30 PM
Solutionexample 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-24-2004 03:59 PM
тАО10-24-2004 03:59 PM
Re: Oracle - replace . with - in column
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-25-2004 09:34 AM
тАО10-25-2004 09:34 AM