OraFAQ Forum: SQL & PL/SQL » substring up to 5 characters after removing special characters

Home » SQL & PL/SQL » SQL & PL/SQL » substring up to 5 characters after removing special characters

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

substring up to 5 characters after removing special characters [message #398837]

Sat, 18 April 2009 03:03

chaituu
Messages: 115
Registered: June 2008

Senior Member

When i run below code after removing special characters and substring upto 5 characters i get this value '1AB' but i want after removing special characters at any time i want 5 characters ;In this case 1ABCM should be required value.

SELECT REGEXP_REPLACE(substr('#1 ABC MAXWELL LTD.',1,5), '[[:punct:]|[:space:]|[:cntrl:]]', '') FROM dual

Report message to a moderator

Re: substring up to 5 characters after removing special characters [message #398838 is a reply to message #398837]

Sat, 18 April 2009 03:12

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

You are substringing then doing the replace.

Report message to a moderator

Re: substring up to 5 characters after removing special characters [message #398839 is a reply to message #398837]

Sat, 18 April 2009 03:15

chaituu
Messages: 115
Registered: June 2008

Senior Member

Thanks for the reply.ya got it.

Report message to a moderator

Re: substring up to 5 characters after removing special characters [message #398984 is a reply to message #398837]

Mon, 20 April 2009 02:50

_jum
Messages: 577
Registered: February 2008

Senior Member

You could remove the special characters simple by replacing all nonword characters:

SELECT REGEXP_REPLACE(substr('#1 ABC MAXWELL LTD.',1,20), '[^[:alnum:]]', '') FROM dual

Report message to a moderator

Previous Topic:	records in loop
Next Topic:	what's wrong with this query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 13:01:51 CST 2024