CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX 		Search Posts SEARCH
POSTS 		Who's Online WHO'S
ONLINE 		Log in LOG
IN

Home: Perl Programming Help: Intermediate:
Would like to seek pointers to solve my problem (I think this could be done using regex)

 


Tendious
New User

Apr 19, 2011, 4:41 AM

Post #1 of 6 (1050 views)
Would like to seek pointers to solve my problem (I think this could be done using regex) Can't Post
Hi all,

I have a string of oracle sql insert statement like,

insert into table_A values (100, 'hello world, TODATE('31-12-2011', 'DD-MM-YYYY'), 'This is an element, element helion');

Is there any way which we can use regular expression or any alternatives to resolve the above string into the following format?

insert into table_A values (
100,
'hello world',
TODATE('31-12-2011','DD-MM-YYYY'),
'This is an element, helion.');

Thanks :)


miller
User

Apr 19, 2011, 5:24 AM

Post #2 of 6 (1047 views)
Re: [Tendious] Would like to seek pointers to solve my problem (I think this could be done using regex) [In reply to] Can't Post
Just have a look at this forum response I provided 4 years ago to someone else trying to do this. Don't have time to give you a customized version as I'm heading to the airport:

http://www.tek-tips.com/viewthread.cfm?qid=1366772

Note: There was a bug introduced with perl version 5.10 where you must predeclare recursive regular expressions using (??{ $code }) or a strictures error gets thrown

Code
our $parens; $parens = qr{
- Miller


(This post was edited by miller on Apr 19, 2011, 6:55 AM)


rovf
Veteran

Apr 19, 2011, 5:29 AM

Post #3 of 6 (1046 views)
Re: [Tendious] Would like to seek pointers to solve my problem (I think this could be done using regex) [In reply to] Can't Post
If I understand you correctly, you want to insert newlines in certain places of the string, to format it into several lines.
This is tricky, since you can't simply insert a \n after each comma (because you don't want a newline inserted withing the argument list of TODATE, or after the word "element".

Unless there are some new regexp features in Perl 5.12 (which I'm not familiar with), I fear you have to do some manual parsing of the line. Maybe Text::Balanced (to be found on CPAN) can help you. I haven't worked with it myself, but I see it often recommended.


miller
User

Apr 19, 2011, 5:38 PM

Post #4 of 6 (1035 views)
Re: [miller] Would like to seek pointers to solve my problem (I think this could be done using regex) [In reply to] Can't Post
It looks and is complicated and it requires perl 5.10 or later because that's when (?PARNO) regex was introduced, but this will do what you want with the text that you provided:

Code
use strict; 
use warnings; 
 
# Single Quotes RE 
our $singleQuotes = qr{ 
	\' (?: 
		(?> [^\'\\]+ ) 
	| 
		\\ . 
	| 
		''	# MySQL allows single quotes to be escaped by doubling 
	)* \' 
}sx; 
 
# Double Quotes RE 
our $doubleQuotes = qr{ 
	\" (?: 
		(?> [^\"\\]+ ) 
	| 
		\\ . 
	)* \" 
}sx; 
 
# Parenthesis RE 
our $parens = qr{ 
	( 
	\w* 
	\( 
	(?: 
		(?> [^\)\(\'\"]+ ) 
	| 
		(?-1) # Allow recursion 
	| 
		$singleQuotes 
	| 
		$doubleQuotes 
	)* 
	\) 
	) 
}sx; 
 
my $sql = q{insert into table_A values (100, 'hello world', TODATE('31-12-2011', 'DD-MM-YYYY'), 'This is an element, element helion');}; 
 
$sql =~ s{ 
	([\(,]\s*) 
	((?:  
		(?> [^\)\,\(\'\"]+ )  
	|  
		$parens  
	|  
		$singleQuotes  
	|  
		$doubleQuotes 
	)*) 
}{$1\n$2}xg; 
 
print $sql; 
 
__END__
- Miller


(This post was edited by miller on Apr 19, 2011, 5:40 PM)


Tendious
New User

Apr 21, 2011, 5:46 AM

Post #5 of 6 (1012 views)
Re: [Tendious] Would like to seek pointers to solve my problem (I think this could be done using regex) [In reply to] Can't Post
Hey miller,

thanks for the solution. They work like magic, now i see the real power of regular expression.

really interesting stuff there, any recommendation on site/books which you think will help u become an advance reg exp user like you?

cheers,
tend
In Reply To


miller
User

Apr 21, 2011, 6:21 AM

Post #6 of 6 (1010 views)
Re: [Tendious] Would like to seek pointers to solve my problem (I think this could be done using regex) [In reply to] Can't Post
Regex's are powerful, but this problem is at the limit of what they should be used for in my opinion. It's of course possible to program all sorts of things, but they are often taken too far. There are cpan modules out there that will often already do a lot of the more common and complicated parsing tasks.

As for as learning regex's, the biggest thing to master is understanding greedy versus non-greedy matching. Knowing when to use .* or .*? is often the most important thing when creating a sleek regex.

After that, you just gotta study the syntax one piece at a time at http://perldoc.perl.org/perlre.html If you understood every part of the regex's used for this problem, you'd be well on your way to doing any problem in my opinion.

Good luck,
- Miller

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives