A rich set of regular expression functions has recently been added in Qlik Sense Cloud and Client Managed. In this article we will present some use cases for this feature and show you how to instantly become a regular expression master.
Regular Expressions (RegEx) is a powerful string pattern matching language that has been available in many programming and scripting languages for decades. Qlik Sense Cloud and the client-managed May 2025 release brings the full power of RegEx to Qlik via a new set of Chart and Load Script functions.
What can you do with RegEx?
- Validate that a field value matches a specific pattern. e.g. Is this a valid phone number or credit card?
- Extract substrings from a larger string.
- Reformat a string to match your rules.
Here is an example to get you started. We will check whether the values in “Phone” correspond to the format allowed for phone numbers in Romania.
The string ‘^(?:\+40|0)[ -]?7\d{2}[ -]?\d{3}[ -]?\d{3}$’ is a regular expression pattern that represents the validation rule for phone numbers in Romania. Like any powerful and syntactically terse programming language, RegEx patterns can look a little daunting at first. Here’s the explanation of what that pattern enforces.
If you want to allow certain separator characters (spaces, hyphens) between groups of digits, you can extend the regular expression. For example, the following expression allows an optional separator between certain groups:
^(?:\+40|0)[ -]?7\d{2}[ -]?\d{3}[ -]?\d{3}$ Explanation:
- (?:\+40|0) – as above, the international prefix or the national 0.
- [ -]? – allows an optional space or hyphen after the prefix.
- 7\d{2} – the first three digits of the mobile number (e.g., “742”).
- [ -]? – optional separator.
- \d{3} – the next three digits.
- [ -]? – optional separator.
- \d{3} – the last three digits.
Here is another example about extracting first and last names from strings, as follows:
James SmithMary JohnsonJohn WilliamsPatricia
BrownRobert Jones-GarciaMichael MillerLinda Davis
The Qlik function ExtractRegEx is the function we want to use. We know enough RegEx off the top of our heads to create a pattern to split on the capital letters and spaces. But how do we deal with the hyphen in “Jones-Garcia”? It would give us serious headaches.
AI is a great tool for generating RegEx patterns, so call on your favorite AI chatbot.
Asking Copilot to generate the pattern
The nice thing about this approach is I don’t have to explicitly state “..and handle hyphens”, I just supply sample data.
Is this pattern correct? You could just plug it into Qlik, but is preferable to validate it first using a RegEx tester. There are a number of free tools available, and regex101.com is the one we suggest you to use.
We do recommend you spend 30 minutes learning the basics of RegEx to make best use of assistants and testing tools.
You may have used the Qlik script SubField function to split a string in multiple rows based on a delimiter such as a comma. But SubField won’t do when comma is part of a quoted string as is common in CSV files. The new SubFieldRegEx function handles this with ease.
Don’t quit if RegEx only gets you 90% of the way there. Stack some Qlik functions like the where clause above to finish the job.
RegEx implementations in other languages use an option flag to indicate a case-insensitive operation. Qlik has opted to use an additional function with the suffix “l” to indicate a case-insensitive operation. So:
- MatchRegEx – case sensitive
- MatchRegExl – case insensitive
A really nice feature of the Qlik MatchRegEx function is that it allows for multiple match patterns to be specified.
The Qlik RegEx functions are documented in the String Functions section. It would have been nice if these functions had their own separate section, but we will summarize the available functions here, noting that they are correct at the time of publication of this article.
- ExtractRegEx() extracts text from an input string expression using the specified regular expression pattern. The function returns a null value if no matches are found.
- IndexRegEx() searches the input string and returns the starting position of the nth occurrence of the specified regular expression pattern.
- MatchRegEx() compares the input string with one or more specified regular expression patterns, and returns the numeric location of the regular expression patterns that match.
- ReplaceRegEx() returns a string after replacing one or more matches between an input string and a specified regular expression pattern.
- SubFieldRegEx() extracts text from an input string expression, using the specified regular expression pattern as a delimiter.
There are a number of useful optional parameters, see the documentation for details, including ReplaceRegExGroup.
Are you going to use these functions a lot? Probably not. But when you need to clean messy data or extract relevant data these functions can be invaluable. I’ll leave you with one last example, extracting data within HTML tags.
Regular Expression Pattern Matching
QlikView script contains many powerful and useful string functions. But sometimes you want the power of Regular Expression (RegExp) pattern matching. QV does not yet provide a native Regular Expression function.
This sample uses a very simple macro that uses VBScript to provide a RegExp pattern matching function in QV script.
Article sourced from here: https://qlikviewcookbook.com and adapted by QQinfo.
For information about Qlik™, click here: qlik.com.
For specific and specialized solutions from QQinfo, click here: QQsolutions.
In order to be in touch with the latest news in the field, unique solutions explained, but also with our personal perspectives regarding the world of management, data and analytics, click here: QQblog !