HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Useful REGEXP examples
While generating codes and working with text there could be necessary to shorten or wrap input text to achieve better readable form.
Following examples of regular expressions usage within SQL queries can be handy.

Following example simply wraps input text to parts with the same lengths including delimiting string between.
The 10 represents length of particular chunks.

Select ''''||regexp_replace('123456789012345678901234567890','(.{10})','\1''||chr(10)||''',1,0,'n')||'''' as WRAPPED from dual;

WRAPPED ----------------------------------------------------------------------- '1234567890'||chr(10)||'1234567890'||chr(10)||'1234567890'||chr(10)||''

Following example simply wraps input text to parts by words with the minimum and maximum line length specified including line feed between.
The 20 represents the maximum length of particular chunks, 15 is the minimum one.

Select regexp_replace('12345678901234567890123 456789012345678 901234 56789012 345678 90','((.{15,20}\s)|(.{20}))','\1'||chr(10),1,0,'n') from dual; 

12345678901234567890 123 456789012345678 901234 56789012 345678 90

Instead of SUBSTR function hiding the fact the string has been shortened we use regular expression placing dots at the end of the shortened string.
The 17 in the example represents length of the final string reduced by three for potential dots. The maximum length of the result string is 20.

Select regexp_replace('12345678901234567890','(^.{17}).{4,}$','\1...',1,1,'n') as SHORTENED from dual;

SHORTENED ----------------------------------------------------------------------- 12345678901234567890

Select regexp_replace('123456789012345678901234567890','(^.{17}).{4,}$','\1...',1,1,'n') as SHORTENED from dual;

SHORTENED ----------------------------------------------------------------------- 12345678901234567...

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky