BigDaddyO Posted December 13, 2022 Share Posted December 13, 2022 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 comment Share on other sites More sharing options...
rcmaehl Posted December 13, 2022 Share Posted December 13, 2022 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 WhyNotWin11Cisco Finesse, Github, IRC UDF, WindowEx UDF Link to comment Share on other sites More sharing options...
TheXman Posted December 13, 2022 Share Posted December 13, 2022 (edited) <snip> Edited December 15, 2022 by TheXman CryptoNG UDF: Cryptography API: Next Gen jq UDF: Powerful and Flexible JSON Processor | jqPlayground: An Interactive JSON Processor Xml2Json UDF: Transform XML to JSON | HttpApi UDF: HTTP Server API | Roku Remote: Example Script About Me How To Ask Good Questions On Technical And Scientific Forums (Detailed) | How to Ask Good Technical Questions (Brief) "Any fool can know. The point is to understand." -Albert Einstein "If you think you're a big fish, it's probably because you only swim in small ponds." ~TheXman Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now