The Regular Expression Generator
By using the regular expression generator, you can generate strings that match a defined pattern. You can use the regular expression generator with any data column that has a data type that accepts a string. These data types are char, varchar, varchar(max), text, nchar, nvarchar, nvarchar(max), ntext, and sysname, and user-defined types that are based on these types. You can also use the regular expression generator with common language runtime user-defined types.
Warning
The regular expression generator cannot guarantee unique values. Therefore, it is not available for columns that must have unique values.
To use the regular expression data generator for a column, you must specify it in the column-details pane of the data generation plan window. After you have specified the regular expression generator, you must set the Expression property in the Properties window. The Expression property contains the pattern that you want the data to match. For more information, see Specify Details of Data Generation for a Column.
The Regular Expression Operators
The Expression property supports the following regular expression operators:
Expression |
Description |
|---|---|
. |
Indicates any character. |
\ |
Indicates that the character that follows should be interpreted literally, instead of as a special character. For example, \. indicates a literal '.'. |
() |
Groups the operators that are inside the parentheses. |
{n} |
Generates n instances of the previous item. For example, a{2} generates 'aa'. |
{n,m} |
Generates at least n instances but no more than m instances of the previous item. For example, a{2,4} generates 'aa', 'aaa', or 'aaaa'. |
{n,} |
Generates n or more instances of the previous item. For example, a{2,} generates 'aa', 'aaa', 'aaaa', 'aaaaa', and so forth. |
* |
Generates 0 or more instances of the previous item. |
+ |
Generates 1 or more instances of the previous item. |
? |
Generates 0 or 1 instance of the previous item. |
| |
Generates an item on either side of the | character. |
[aeiou] |
Generates any character inside the brackets. |
[a-z] |
Generates any character in the specified range of characters. |
[^aeiou] |
Generates any character except those inside the brackets. |
Sample Regular Expressions
The following strings are examples of common regular expressions:
Expression |
Description |
|---|---|
(F|M) |
A simple representation of gender. |
[1-9][0-9]{2,2}-[1-9][0-9]{2,2}-[0-9]{4,4} |
A simple phone number, represented as 800-555-8446 |
\+1 (425|206)-[1-9][0-9]{2,2}-[0-9]{4,4} |
International notation for a Seattle-area phone number. |
[1-9][0-9]{4}-[0-9]{4} |
Zip code plus four (such as 98008-2405) |
[1-6]{1}[0-9]{1,3} (SE|NE|NW|SW) [1-2]{1}[0-9]{1,2}th (ST|CT|PL|AVE), (Redmond, WA 9805[0-9]|Bellevue, WA 9800[1-9]|Sammamish, WA 9807[0-9]|Seattle, WA 9806[0-9]|Issaquah, WA 9808[0-9]) |
A simple street address. |
Seattle|(New York)|Boston|Miami|Beijing|(Los Angles)|London|Paris |
A list of city names. |
[a-z]{5,8}@(hotmail\.com|msn\.com|[a-z]{3,8}\.(com|net|org)) |
A simple e-mail address. |
[1-9][0-9]{3} [0-9]{4} [0-9]{4} [0-9]{4} |
Credit-card number. |
See Also
Concepts
Regular Expression Language Elements