Jump to content

StringRegExpReplace , not within ( )


Recommended Posts

I'm trying to convert a sql that's in a single line back into a nicely formatted string but i'm having problems with the comma's when they are within a command such as SUBSTR

for an Example:  Select SUBSTR(sx.YMD, 5, 2) || '/' || SUBSTR(sx.YMD, 7, 2) || '/' || SUBSTR(sx.YMD, 1, 4) as RecvDt, TRIM(sx.MBR) as MbrNbr From Services

currently comes out as 

Select SUBSTR(sx.YMD,
     5,
     2) || '/' || SUBSTR(sx.YMD,
     7,
     2) || '/' || SUBSTR(sx.YMD,
     1,
     4) as RecvDt,
     TRIM(sx.MBR) as MbrNbr
From Services

Where it should show as 

Select SUBSTR(sx.YMD, 5, 2) || '/' || SUBSTR(sx.YMD, 7, 2) || '/' || SUBSTR(sx.YMD, 1, 4) as RecvDt,
     TRIM(sx.MBR) as MbrNbr
From Services

 

This is what i've coded so far.  I'm pretty sure I need to use a StringRegExpReplace to replace the , only when it's not within a ( ) but i'm not sure how to do it.  Anyone have some RegEx pointers?

$sSQL = "Select SUBSTR(sx.YMD, 5, 2) || '/' || SUBSTR(sx.YMD, 7, 2) || '/' || SUBSTR(sx.YMD, 1, 4) as RecvDt, TRIM(sx.MBR) as MbrNbr From Services"
$sSQL = StringReplace($sSQL, ",", "," & @CRLF & @TAB)

;Only replace , with , & @CRLF & @TAB if that , is not within ( )
;~ StringRegExpReplace($sSQL, "

$sSQL = StringReplace($sSQL, " Select ", @CRLF & "Select" & @CRLF & @TAB)
$sSQL = StringReplace($sSQL, "From ", @CRLF & "From ")
$sSQL = StringReplace($sSQL, "Join ", @CRLF & "Join ")
$sSQL = StringReplace($sSQL, "Left Join ", @CRLF & "Left Join ")
$sSQL = StringReplace($sSQL, "Inner Join ", @CRLF & "Inner Join ")
$sSQL = StringReplace($sSQL, "Outer Join ", @CRLF & "Outer Join ")
$sSQL = StringReplace($sSQL, "ON ", @CRLF & @TAB & "ON ")
$sSQL = StringReplace($sSQL, "Where ", @CRLF & "Where ")
$sSQL = StringReplace($sSQL, "AND ", @CRLF & "AND ")

ConsoleWrite(@CRLF & $sSQL & @CRLF & @CRLF)

Thanks, Mike

Link to post
Share on other sites

You're likely looking for:

[^(] - Don't match (

[^)] - Don't match )

 

My UDFs are generally for me. If they aren't updated for a while, it means I'm not using them myself. As soon as I start using them again, they'll get updated.

My Projects

WhyNotWin11
Cisco FinesseGithubIRC UDFWindowEx UDF

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...