The Regex feature in Azure SQL DB follows the POSIX<\/strong> standard and is compatible with the standard regex syntax and supports a variety of regex functions, such as REGEXP_LIKE, REGEXP_COUNT, REGEXP_INSTR, REGEXP_REPLACE, <\/strong>and REGEXP_SUBSTR<\/strong>. The feature also supports case sensitivity, character classes, quantifiers, anchors, and capturing groups. The feature is available for all Azure SQL DB service tiers and offerings.<\/p>\n Here is the list of Regex functions that are being introduced:<\/p>\n We\u2019ll use the following table as an example to demonstrate the usage of these functions. The table contains some information about employees, such as their names, email addresses, and phone numbers.<\/p>\n <\/p>\n The following are some examples of using Regex functions in SQL queries.<\/p>\n This function returns True if the input string matches the regex pattern, and False otherwise. You can use it to filter rows based on a regex condition and to apply check constraints to ensure that the data fulfils the specified criteria.<\/p>\n Let\u2019s create Employee table with some records and Check constraints for Email<\/span> and Phone_Number<\/span> columns:<\/p>\n Insert a new row into the Employees table with the following values to validate the Check constraints:<\/p>\n Filter rows based on regex condition:<\/p>\n For example, the following query returns all the rows from the Employees table where the email address domain ends with ‘.com’.<\/p>\n Results:<\/p>\n <\/p>\n This function returns the number of times a regex pattern matches in the input string. You can use it to count the occurrences of a pattern within a string. For example, the following query returns the number of vowels in each Employee\u2019s name.<\/p>\n Results:<\/p>\n <\/p>\n This function returns the starting or ending position, based on the specified option, of the given occurrence of a regular expression pattern in a string. You can use it to locate the index of a pattern within a string.<\/span>\u00a0For example, the following query returns the position of ‘@’ in each Employee’s email address.<\/p>\n Results:<\/p>\n <\/p>\n This function returns a modified string replaced by a \u2018replacement string\u2019, where occurrence of the regular expression pattern found.\u00a0<\/span>You can use it to modify or transform text data based on a regex pattern. For example, the following query returns the phone number of each Employee in a standardized format.<\/p>\n Results:<\/p>\n <\/p>\n This function returns the substring that matches the regular expression pattern from a string. You can use it to extract parts of a string based on a regex pattern. For example, the following query returns the domain name of each employee’s email address.<\/p>\n Results:<\/p>\n <\/p>\n We are currently accepting requests from customers who would like to participate in the private preview and try out the regex feature. If you are interested, please fill out this form<\/a>: https:\/\/aka.ms\/regex-preview-signup<\/a><\/p>\nFeature Exploration<\/h2>\n
\n
Examples<\/h2>\n
\n\n
\n ID\u00a0<\/strong><\/td>\n Name\u00a0<\/strong><\/td>\n Email\u00a0<\/strong><\/td>\n Phone_Number\u00a0<\/strong><\/td>\n<\/tr>\n \n 101<\/td>\n John Doe<\/td>\n john@contoso.com<\/td>\n 123-456-7890<\/td>\n<\/tr>\n \n 102<\/td>\n Alice Smith<\/td>\n alice@fabrikam.com<\/td>\n 234-567-8901<\/td>\n<\/tr>\n \n 103<\/td>\n Bob Johnson<\/td>\n bob@fabrikam.net<\/td>\n 345-678-9012<\/td>\n<\/tr>\n \n 104<\/td>\n Eve Jones<\/td>\n eve@contoso.com<\/td>\n 456-789-0123<\/td>\n<\/tr>\n \n 105<\/td>\n Charlie Brown<\/td>\n charlie@contoso.co.in<\/td>\n 567-890-1234<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n REGEXP_LIKE<\/h3>\n
-- Create Employees table with some records and check constraints for Email and Phone_Number columns\r\nDROP TABLE IF EXISTS Employees\r\nCREATE TABLE Employees (\r\n ID INT IDENTITY(101,1),\r\n [Name] VARCHAR(150),\r\n Email VARCHAR(320)\r\n CHECK (REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$')),\r\n Phone_Number VARCHAR(20)\r\n CHECK (REGEXP_LIKE (Phone_Number, '^(\\d{3})-(\\d{3})-(\\d{4})$'))\r\n);\r\n-- Insert some sample data\r\nINSERT INTO Employees ([Name], Email, Phone_Number) VALUES\r\n ('John Doe', 'john@contoso.com', '123-456-7890'),\r\n ('Alice Smith', 'alice@fabrikam.com', '234-567-8901'),\r\n ('Bob Johnson', 'bob@fabrikam.net','345-678-9012'),\r\n ('Eve Jones', 'eve@contoso.com', '456-789-0123'),\r\n ('Charlie Brown', 'charlie@contoso.co.in', '567-890-1234');<\/code><\/pre>\n--Failure: \r\nINSERT INTO Employees ([Name], Email, Phone_Number) VALUES \r\n ('Demo Data', 'demo@contoso@com', '123-456_7890') \r\n\r\n--Success: \r\nINSERT INTO Employees ([Name], Email, Phone_Number) VALUES \r\n ('Demo Data', 'demo@contoso.com', '123-456-7890') <\/code><\/pre>\n-- find all the employees whose email addresses end with .com\r\nSELECT [Name], Email\r\nFROM Employees\r\nWHERE REGEXP_LIKE(Email, '\\.com$');<\/code><\/pre>\n\n\n
\n Name<\/td>\n Email<\/td>\n<\/tr>\n \n John Doe<\/td>\n john@contoso.com<\/td>\n<\/tr>\n \n Alice Smith<\/td>\n alice@fabrikam.com<\/td>\n<\/tr>\n \n Eve Jones<\/td>\n eve@contoso.com<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n REGEXP_COUNT<\/h3>\n
-- for each employee, count the number of vowels in their name\r\nSELECT [Name], REGEXP_COUNT([Name], '[AEIOU]',1,'i') AS Vowel_Count\r\nFROM Employees;<\/code><\/pre>\n\n\n
\n Name\u00a0<\/strong><\/td>\n Vowel_Count\u00a0<\/strong><\/td>\n<\/tr>\n \n John Doe<\/td>\n 3<\/td>\n<\/tr>\n \n Alice Smith<\/td>\n 4<\/td>\n<\/tr>\n \n Bob Johnson<\/td>\n 3<\/td>\n<\/tr>\n \n Eve Jones<\/td>\n 4<\/td>\n<\/tr>\n \n Charlie Brown<\/td>\n 4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n REGEXP_INSTR<\/h3>\n
-- for each employee, show their name, email, and the position of the @ sign in their email\r\nSELECT [Name], Email, REGEXP_INSTR(email, '@') AS Position_of_@\r\nFROM Employees;<\/code><\/pre>\n\n\n
\n Name\u00a0<\/strong><\/td>\n Email\u00a0<\/strong><\/td>\n Position_of_@\u00a0<\/strong><\/td>\n<\/tr>\n \n John Doe<\/td>\n john@contoso.com<\/td>\n 5<\/td>\n<\/tr>\n \n Alice Smith<\/td>\n alice@fabrikam.com<\/td>\n 6<\/td>\n<\/tr>\n \n Bob Johnson<\/td>\n bob@fabrikam.net<\/td>\n 4<\/td>\n<\/tr>\n \n Eve Jones<\/td>\n eve@contoso.com<\/td>\n 4<\/td>\n<\/tr>\n \n Charlie Brown<\/td>\n charlie@contoso.co.in<\/td>\n 8<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n REGEXP_REPLACE<\/h3>\n
-- format the phone numbers in the Employees table to the format (XXX) XXX-XXXX.\r\nSELECT Phone_Number, REGEXP_REPLACE(Phone_Number, '(\\d{3})-(\\d{3})-(\\d{4})', '(\\1) \\2-\\3',1) AS Phone_Format\r\nFROM Employees;<\/code><\/pre>\n\n\n
\n EMP_PHONE\u00a0<\/strong><\/td>\n PHONE_FORMAT\u00a0<\/strong><\/td>\n<\/tr>\n \n 123-456-7890<\/td>\n (123) 456-7890<\/td>\n<\/tr>\n \n 234-567-8901<\/td>\n (234) 567-8901<\/td>\n<\/tr>\n \n 345-678-9012<\/td>\n (345) 678-9012<\/td>\n<\/tr>\n \n 456-789-0123<\/td>\n (456) 789-0123<\/td>\n<\/tr>\n \n 567-890-1234<\/td>\n (567) 890-1234<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n REGEXP_SUBSTR<\/h3>\n
-- for each employee, show the domain of their email address\r\nSELECT [Name], Email, REGEXP_SUBSTR(email, '@(.+)$', 1, 1,'c',1) AS Domain\r\nFROM Employees;<\/code><\/pre>\n\n\n
\n Name\u00a0<\/strong><\/td>\n Email\u00a0<\/strong><\/td>\n Domain\u00a0<\/strong><\/td>\n<\/tr>\n \n John Doe<\/td>\n john@contoso.com<\/td>\n contoso.com<\/td>\n<\/tr>\n \n Alice Smith<\/td>\n alice@fabrikam.com<\/td>\n fabrikam.com<\/td>\n<\/tr>\n \n Bob Johnson<\/td>\n bob@fabrikam.net<\/td>\n fabrikam.net<\/td>\n<\/tr>\n \n Eve Jones<\/td>\n eve@contoso.com<\/td>\n contoso.com<\/td>\n<\/tr>\n \n Charlie Brown<\/td>\n charlie@contoso.co.in<\/td>\n contoso.co.in<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Private Preview Sign-up<\/h2>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2024\/03\/NewQRCode-for-Regex-in-Azure-SQL-Database-Private-Preview-Sign-up-300x300.png\")
<\/a><\/p>\n