Home » SQL & PL/SQL » SQL & PL/SQL » substring up to 5 characters after removing special characters
substring up to 5 characters after removing special characters [message #398837] Sat, 18 April 2009 03:03 Go to next message
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
Re: substring up to 5 characters after removing special characters [message #398838 is a reply to message #398837] Sat, 18 April 2009 03:12 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You are substringing then doing the replace.
Re: substring up to 5 characters after removing special characters [message #398839 is a reply to message #398837] Sat, 18 April 2009 03:15 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Thanks for the reply.ya got it.
Re: substring up to 5 characters after removing special characters [message #398984 is a reply to message #398837] Mon, 20 April 2009 02:50 Go to previous message
_jum
Messages: 508
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
Previous Topic: records in loop
Next Topic: what's wrong with this query
Goto Forum:
  


Current Time: Sat Dec 03 00:56:50 CST 2016

Total time taken to generate the page: 0.10679 seconds