Regex Scripting

Regex

Posted by John Liu on Saturday, November 27, 2021

Some Regex syntax

Find quoted text

"(.*?)"

Lookahead: matches “x” only if “x” is followed by “y”

x(?=y)

Negative lookahead: matches “x” only if “x” is NOT followed by “y”

x(?!y)

Lookbehind: matches “x” only if “x” is preceded by “y”

(?=y)x

Negative lookbehind: matches “x” only if “x” is NOT preceded by “y”

(?!y)x

CSV file line columns counts

Using comma as delimiter.

,|(".*?"(,(?!$))?)

break above down as:

,       a standard comma
|       or
(       start of group
".*?"   quote of a string, for value contains comma, CSV will quote the entire string
(       start of inner group
,(?!$)  a coma that not last character of the line. If comma is last character of the line, it needs to be counted separatly
)?      end of inner group, and this group is optional.
)       end of group

csv file comma delimited section

[^,(?<(".*?"(,(?!$))?))]*,|(".*?"(,(?!$))?)

find line contains 5 commas, counts comma in quoted string

^([^,]*,){5}[^,]*$

break above down as:

^       #Start of string
(       #Start of group
[^,]*   #Any character except comma, zero or more times
,       #A comma
){5}    #End and repeat the group 5 times
[^,]*   #Any character except comma, zero or more times again
$       #End of string

find string contains 5 commas, exclude counts comma in quoted string

(([^,(".*?")]*,)|(".*?",)){5}

break above down as:

(                   #start of group
([^,(".*?")]*,)     #any non-quoted string end with comma
|                   #or
(".*?",)            #quoted string end with comma
)                   #end of group
{5}                 #repeat the group 5 times

find string between two specific words

(?s)^To: (.*)(?=^Subject:)

break above down as:

(?s)                #using Singleline mode
^To:                #line start with To: 
(.*)                #any characters
(?=^Subject:)       #lookahead for line start with Subject:, and stop just before the last Subject:

extract all To and Cc email address

^To: .*(\n|\r|\r\n)(^\s+.*(\n|\r|\r\n))*(^CC: .*(\n|\r|\r\n)(^\s+.*(\n|\r|\r\n)?)*)?(?=^Subject:)?

break above down as:

^To: .*(\n|\r|\r\n)     #find line start with To: (we use \n|\r|\r\n so we don't need the (?s) for Singleline mode)
(^\s+.*(\n|\r|\r\n))*   #zero or more lines that start with space following above line. This is for To: addresses have been split into muliple lines
(^CC: .*(\n|\r|\r\n)(^\s+.*(\n|\r|\r\n)?)*)?    #find 0 or 1 line start with Cc:, include Cc: addresses have been split into multiple lines
(?=^Subject:)?           #look ahead and stop at line start with Subject:

Useful checksheet sites:

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions/Cheatsheet https://stackoverflow.com/questions/39636124/regular-expression-works-on-regex101-com-but-not-on-prod

FEATURED TAGS
ai api automation availability availability-sets availability-zones aws-vm azure azure-automation-runbook azure-blob azure-cosmos-db azure-data-lake azure-deployment azure-function-app azure-functions azure-openai azure-sign-in azure-site-recovery azure-sql-database azure-subscription azure-vm base64 certificate change-data-capture change-tracking chrome clr container cte data-api-builder data-conversion data-gateway database-mail database-role database-size date-table dax db-config derived-table diagram direct-query disk-management disk-space docker downtime dtc dynamic-m-parameter embedding encrypted-connection excel excel-online excel-online-for-business execution-plan extended-events external-data fabric fabric-capacity failover-cluster fk geometry hierarchy httpwebrequest hugo hyper-v incognito-mode index infrastructure inline-tvf json kql lakehouse linked-server live-query-statistics locking m machine-learning machine-learning-model machine-learning-services master-key mcp mdx memory memory-grant mermaid mirrored-sql-server network network-card network-category office-script onedrive onnx-runtime openrowset p2v parquet performance polybase power-automate power-bi power-bi-report-tricks power-platform power-query powershell printer public-ip-address pyspark python qgis qt-designer query-performance query-plan query-troubleshooting r regex replication route s3 schema-design scripting self-signed-certificate server-role sharepoint snowflake software-development sofware-development spark sql sql-2025 sql-agent sql-availability-group sql-error sql-failover-cluster-instance sql-index sql-openjson sql-permission sql-recovery sql-script sql-security sql-server sql-server-admin sql-server-config sql-statistics ssis ssisdb ssl ssl/tls-error ssms table-expression tempdb terraform tips troubleshooting unicode view visual-studio visual-studio-code vmware wait-statistics wi-fi-connection-issue windows-settings