OraFAQ Forum: SQL & PL/SQL » How to remove hyphens from a string

Home » SQL & PL/SQL » SQL & PL/SQL » How to remove hyphens from a string

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to remove hyphens from a string [message #238376]

Thu, 17 May 2007 14:08

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.

Report message to a moderator

Re: How to remove hyphens from a string [message #238377 is a reply to message #238376]

Thu, 17 May 2007 14:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Read The Fine Manual on the TRANSLATE function

Report message to a moderator

Re: How to remove hyphens from a string [message #238378 is a reply to message #238376]

Thu, 17 May 2007 14:15

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

For this case I prefer REPLACE. Wink

Regards
Michel

Report message to a moderator

Re: How to remove hyphens from a string [message #238381 is a reply to message #238376]

Thu, 17 May 2007 14:28

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is why I prefer REPLACE.

Regards
Michel

Report message to a moderator

Re: How to remove hyphens from a string [message #238391 is a reply to message #238376]

Thu, 17 May 2007 15:06

BlackSwan
Messages: 26766
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 18:37:24 CST 2024