Home » SQL & PL/SQL » SQL & PL/SQL » How to remove hyphens from a string
How to remove hyphens from a string [message #238376] Thu, 17 May 2007 14:08 Go to next message
srajan72
Messages: 20
Registered: February 2006
Junior Member
How do I reformat a string to remove all instances of '-' (hypen)?

Example: 'ABC-DE-FGHI' should translate to 'ABCDEFGHI'.

I cannot use SUBSTR because the source string can be in different formats.

I'd appreciate any help.
Re: How to remove hyphens from a string [message #238377 is a reply to message #238376] Thu, 17 May 2007 14:11 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Read The Fine Manual on the TRANSLATE function
Re: How to remove hyphens from a string [message #238378 is a reply to message #238376] Thu, 17 May 2007 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For this case I prefer REPLACE. Wink

Regards
Michel
Re: How to remove hyphens from a string [message #238381 is a reply to message #238376] Thu, 17 May 2007 14:28 Go to previous messageGo to next message
srajan72
Messages: 20
Registered: February 2006
Junior Member
Thanks for your replies.

I tried TRANSLATE, but since I needed to replace '-' with nothing, I tried using NULL (e.g., TRANSLATE(str1,'-',NULL) with obvious undesired result.

I'd really appreciate if you can tell me how I can use TRANSLATE here.

By the way - REPLACE works when I tried REPLACE(str1,'-',NULL)

Thanks once again for your help.

[Updated on: Thu, 17 May 2007 14:29]

Report message to a moderator

Re: How to remove hyphens from a string [message #238382 is a reply to message #238381] Thu, 17 May 2007 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is why I prefer REPLACE.

Regards
Michel
Re: How to remove hyphens from a string [message #238391 is a reply to message #238376] Thu, 17 May 2007 15:06 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
as requested
SQL> create table test_tr(ssn varchar2(11));

Table created.

SQL> insert into  test_tr values('ABC-DE-FGHI');

1 row created.

SQL> commit;

Commit complete.

SQL> select translate(ssn,'ABCDEFGHI-','ABCDEFGHI') from test_tr;

TRANSLATE(SSN,'ABCDEFGHI-','ABCDEFGHI')
--------------------------------------------
ABCDEFGHI


http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions196.htm#sthref2394

The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string.
If these extra characters appear in char, then they are removed from the return value.

[Updated on: Thu, 17 May 2007 15:16] by Moderator

Report message to a moderator

Previous Topic: difference between B-Tree and Bitmap indexes
Next Topic: Deleteing rows using with clause
Goto Forum:
  


Current Time: Thu Dec 08 16:19:34 CST 2016

Total time taken to generate the page: 0.08240 seconds