Applies to
ApexSQL Generate
Summary
This article explains how to generate values in ApexSQL Generate using Regular expression generator.
Description
The Regular expression generator covers the widest spectrum of data types and provides a user with very high level of data customization. It is presented for all data types except for timestamp, xml and image. This generator creates and inserts values by following a pattern provided by user. The pattern itself is a sequence of “metacharacters” (characters that in Regular expression have a special meaning) and characters, that is processed in order to provide a matching results as values for database population.
Additionally, the Regular expression generator is enhanced with the ability to insert predefined lists, incremental values (numeric, string and binary) or even values from other columns in the same table.
By clicking the Save button appended to the generator drop-down list, the created patterns can be saved just as in any other generator. When you wish to reuse it, select User defined generator and the new drop-down list will appear with all the saved User defined files:
The Regular expression generator has 3 options which are present for any given SQL data type:
- Randomize data by:
Seed
– this value determines the sequence of generated values. Each seed value is related to the specific sequence of data
Timestamp – creates unique set of values for the given column -
Unique – ensures that all the values in the column are unique and that there will not be repetition of data. This option is automatically selected for columns that requires data uniqueness (primary key columns, for example)
-
Allow null – inserts the NULL values into the generated sequence. The amount of NULL values is determined as the percentage of requested row number for the table containing the selected column. The percentage value is assigned through the % of null option.
Predefined lists
The Regular expression generator has an option for inserting already defined values combined with regular expressions. One of these options is insertion of Predefined lists. This feature allows user to include lists of values from the Predefined generator:
This pattern will create the following values:
As noticeable from the image above, the selected predefined list is presented in the expression editor in quotation marks and with the dollar mark ($) in front of it. The dollar mark in this case acts like a placeholder for predefined lists. This implies that when creating a pattern, the Regular expression syntax must be followed.
For example, if a three digit number must be inserted, an arbitrary three digit number, can be typed in, but in that case, that same number will be inserted in all the rows of the selected column. To get a various values of three digit numbers for each row there are several ways to write a pattern that will create these:
[0-9][0-9][0-9] – This will create a range of number from 000 to 999. Each square bracket presents a range of digit and it will be sufficient to reduce the range in the first square bracket in order to reduce the overall range of numbers inserted:
[1-3][0-9][0-9] – This will create a range of numbers from 000 to 399:
There are further simplifications to creating a three digit number:
[0-9]{3} – Now range of digit is 0 to 9, but in curly bracket is number 3 which defines the number of repetitions of the given range, i.e. the count of digits in the number. This expression will provide the same range of numbers as [0-9][0-9][0-9].
When defining the range (in square brackets) it must be in ascending order.
Some basic special characters are contained in the Snippet option:
There are many ways to design test data using Regular expression and more about syntax and creating patterns can be found on this link.
|
Quick tip: If not accustomed to the Regular expression syntax, make sure to use the Snippet option |
Custom lists
The Regular Expression generator also has an option of loading customized lists of values from Text e.g. .txt or CSV e.g. .csv format. The option to load these can be found on the bottom of the menu of the List drop-down button (see below). This option leads to another one; The Separator option allows specifying character which will be interpreted as a separator in .txt or .csv file formats.
Defining a separator increases possibilities of manipulating the data in .csv files. For example, the letter ‘a’ can be defined as a separator, in which case values between two letters ‘a’ will be inserted in a SQL column, even if in the .csv file that would mean partial or complete merging of several columns or entire row:
Switching amongst the columns contained in the .txt or .csv file is achieved in the Expression editor:
The number defined in the square brackets, at the end of the file path, determines which column is used for population. By default that number is [0] which will insert the first column (defined by the separator) from the file; [1] will insert the second column, etc.
Populating with values from another column
The Regular expression generator provides the user with an option to reference another column from the same table, and with that action, to transfer data to the current column.
From the point of the Regular expression syntax, this has a form of the name of the column typed between two percentage marks:
As seen in the image above, multiple columns can be inserted in the expression editor. In the image bellow are shown the results of this expression:
|
Quick tip: Note that special characters, in order to be recognized as literals by the Regular expression engine, must be typed with the backslash first. E.g. \. instead of . |
Another useful feature implemented in the Regular expression generator is the Counter option. It imitates the Incremental generator and when used inserts the expression that will create increment or decrement sequence of number, letters or combination of those two (binary data type):
Unlike the Incremental generator, the Counter feature in the Regular expression is only applicable to string, numeric and binary data types, where the Incremental covers date and time, spatial, hierarchyid and uniqueidentifier data types. Also, there is a possibility of defining increment or decrement step in the Incremental generator which is not given with the Counter feature. However, the Regular expression in general can cover all the data types as the Incremental, with the ability for defining increment or decrement step.
FAQ’s about the Regular expression generator:
Q: For which data types can I use the Regular expression generator?
A: The regular expression generator can be used for any SQL data type e.g. int, varchar, datetime, etc., except server assigned data types e.g. identity, xml, timestamp, computed, primary key
Q: Which columns can be referred to via the Regular expression generator?
A: This depends on the data type of the current column. All the columns from the current table are presented in the Column drop-down button, but only the ones with the matching data type can have their values transferred to the current column.
Q: Does the Snippet feature contains all the Regular expression special characters?
A: No. The Snippet feature contains special characters that are most commonly used. In addition to that it allows inserting date and datetime expression for user’s convenience.
Q: Does the List feature contains all the predefined lists that are contained in the Predefined generator?
A: Yes, but just as in the Predefined generator, only the ones that can be used for the given data type will be presented.
