regex to match RTRIM(LTRIM(xx)) = xx

Question

I am trying to jot down regex to find where I am using ltrim rtrim in where clause in stored procedures.

the regex should match stuff like:

RTRIM(LTRIM(PGM_TYPE_CD))= 'P'))

RTRIM(LTRIM(PGM_TYPE_CD))='P'))

RTRIM(LTRIM(PGM_TYPE_CD)) = 'P'))

RTRIM(LTRIM(PGM_TYPE_CD))= P

RTRIM(LTRIM(PGM_TYPE_CD))= somethingelse))

etc...

I am trying something like...

.TRIM.*\)\s+

Answer 1

This what you want:

[LR]TRIM\([RL]TRIM\([^)]+\)\)\s*=\s*[^)]+\)*

?

What's that doing is saying:

[LR]    # Match single char, either "L" or "R"
TRIM    # Match text "TRIM"
\(      # Match an open parenthesis
[RL]    # Match single char, either "R" or "L" (same as [LR], but easier to see intent)
TRIM    # Match text "TRIM"
\(      # Match an open parenthesis   
[^)]+   # Match one or more of anything that isn't closing parenthesis
\)\)    # Match two closing parentheses
\s*     # Zero or more whitespace characters
=       # Match "="
\s*     # Again, optional whitespace (not req unless next bit is captured)
[^)]+   # Match one or more of anything that isn't closing parenthesis
\)*     # Match zero or more closing parentheses. 

If this is automated and you want to know which variables are in it, you can wrap parentheses around the relevant parts:

[LR]TRIM\([RL]TRIM\(([^)]+)\)\)\s*=\s*([^)]+)\)*

Which will give you the first and second variables in groups 1 and 2 (either \1 and \2 or $1 and $2 depending on regex used).

Answer 2

[RL]TRIM\s*\( Will look for R or L followed by TRIM, any number of whitespace, and then a (

Answer 3

How about something like this:

.*[RL]TRIM\s*\(\s*[RL]TRIM\s*\([^\)]*)\)\s*\)\s*=\s*(.*)

This will capture the inside of the trim and the right side of the = in groups 1 and 2, and should handle all whitespace in all relevant areas.