Introduction
When working with SQL Server Integration Services (SSIS), Regular expressions (Regex) can be extremely effective for transforming and cleaning data. This guide explores common Regular expression (Regex) patterns in SSIS, with a special focus on lookaheads and lookbehinds (called lookaround), powerful yet often underutilized features that enable conditional pattern matching.
A Regex is a sequence of characters that defines a search pattern. For building and testing patterns, tools like Regex Hero or Regex101 are highly recommended.
Lookahead & Lookbehind
Lookaheads and lookbehinds are zero-width assertions, meaning they match a pattern based on surrounding context—without including that context in the final result.
Positive Lookahead
MySearchedQuery(?=-)
Matches "MySearchedQuery" only if it is followed by a hyphen (-).
Match: MySearchedQuery-
No Match: MySearchedQuery
Positive Lookbehind
(?<=-)\d+
Matches one or more digits only if preceded by a hyphen.
Match: -123 (matches 123)
No Match: 123
Negative Lookahead
MySearchedQuery(?!-)
Matches "MySearchedQuery" only if not followed by a hyphen.
Match: MySearchedQuery
No Match: MySearchedQuery-
Negative Lookbehind
(?<!-)MySearchQuery
Matches "MySearchQuery" only if not preceded by a hyphen.
Match: MySearchQuery
No Match: -MySearchQuery
Test these examples interactively using Regex Hero:
Most common Regex patterns
| Pattern | Description | Example Match |
|---|---|---|
^ |
Start of line or string | ^abc in abc123 |
$ |
End of line or string | abc$ in 123abc |
. |
Any character (except newline) | a.c → abc, a-c |
\d |
Digit [0-9] |
\d+ → 123 |
\w |
Word character [a-zA-Z0-9_] |
\w+ → abc_123 |
\s |
Whitespace | \s+ → spaces, tabs |
[abc] |
One of a, b, or c | [aeiou] → vowels |
[^abc] |
Not a, b, or c | [^0-9] → not a digit |
* |
0 or more occurrences | a* → [empty], a, aaa |
+ |
1 or more occurrences | a+ → a, aaa |
? |
0 or 1 occurrence | a? → [empty], a |
{n} |
Exactly n occurrences | \d{4} → 2025 |
{n,} |
At least n occurrences | \d{2,} → 12, 123 |
{n,m} |
Between n and m occurrences | \d{2,4} → 12, 1234 |
Real-world examples
| Pattern | Use Case | Example Match |
|---|---|---|
\d{4}-\d{2}-\d{2} |
Date format YYYY-MM-DD |
2025-07-10 |
\b\w+@\w+\.\w{2,}\b |
Email address | john@example.com |
https?://[^\s]+ |
URL | https://openai.com |
^\+?\d{10,15}$ |
International phone number | +1234567890 |
(?<=<title>).*?(?=</title>) |
Extract between <title> tags |
My Page from <title>My Page</title> |
(?<!\d)\d{5}(?!\d) |
Validates 5-digit zip code only | 90210 |
Using in SSIS
You can use Regex across several ZappySys SSIS components:
1. Regular Expression Parser Task
Parse SSIS variable values or file contents using full Regex support.
2. Excel Source
Filter sheets or extract sheet names using Regex patterns.
3. HTTP Connection
Extract authentication tokens or dynamic values from API responses in REST API Task / JSON Source.
4. JSON Source
Replace or transform string values using Regex in pre-processing (modifying JSON before using it in JSON Source).
Conclusion
Learning how to apply Regex in SSIS unlocks powerful options for text parsing, validation, and transformation. With ZappySys SSIS connectors that have built-in Regex support, like the SSIS Regex Parser Task, you can implement precise logic with just a single line of Regex. Mastering Regex lookaheads and lookbehinds enables you to fully control your data and streamline your ETL operations.
Contact us
If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket.