RegEx Is Your Friend

The power of VSTE DBPro data generation, lays in the fact you can generate data that closely matches your domain values allowed in your environment. The problem is that knowledge of the allowed and disallowed domain values can not always be derived from the schema, so you have to go in to the designer and configure the generators for your columns.

One way to generate data that matches you needs is to use the Regular Expression generator, but using regular expressions can be intimidating, so here a simple list of example RegEx expressions that you can "re-use" in your environment.

Simple gender representation:

  • (F)emale or (M)ale using a single character representation (F|M)
    • F|M
  • Mr or Mrs
    • Mr|Mrs

Various phone number formats:

  • Simple phone number formatted like 267-820-8446 (separated by dashes)
    • [1-9][0-9]{2,2}-[1-9][0-9]{2,2}-[0-9]{4,4}
  • Simple phone number formatted like (267) 820-8446
    • \([1-9][0-9]{2,2}\) [1-9][0-9]{2,2}-[0-9]{4,4}
  • Phone number within area code 425
    • 425-[1-9][0-9]{2,2}-[0-9]{4,4}
  • Phone number within area 425 or 206
    • (206|425)-[1-9][0-9]{2,2}-[0-9]{4,4}
  • Internal notation using country code 1 and area code 425 or 206
    • \+1 (425|206)-[1-9][0-9]{2,2}-[0-9]{4,4}

ZIP codes:

  • Simple ZIP code 5 digits plus 4 digits separated by a dash (like 98008-2405)
    • [1-9][0-9]{4}-[0-9]{4}
  • ZIP code that starts in area 98xxxx
    • 98[0-9]{3}
  • Add state to zip code like WA 98322-1001
    • WA 98[0-9]{3,3}-[1-9]{1,1}[0-9]{3,3}

 Address:

  • Create a simple address
    • [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])

City names:

  • List of city names
    • Seattle|(New York)|Boston|Miami|Beijing|(Los Angles)|London|Paris

First & last names:

  • List of first names
    • (Pete|Tom|Mary|Larry|Lisa|Brain|David|Jeff|Amy)
  • List of last names
    • (Johnson|Smith|Good|Anderson|Baker|Crawford)
  • List of name (first + last)
    • (Pete|Tom|Mary|Larry|Lisa|Brain|David|Jeff|Amy) (Johnson|Smith|Good|Anderson|Baker|Crawford)

E-mail addresses:

  • Simple email address
    • [a-z]{5,8}@(hotmail\.com|msn\.com|[a-z]{3,8}\.(com|net|org))

Social Security number:

  • [1-9][0-9]{2}-[0-9]{2}-[0-9]{4}

Credit card number:

  • [1-9][0-9]{3} [0-9]{4} [0-9]{4} [0-9]{4}

Credit card names:

  • AMEX|VISA|MASTER

Shippers:

  • DHL|FedEx|UPS

 

An Example:

Table structure:

CREATE TABLE [dbo].[Customer]
(

CustomerID    INT IDENTITY(1, 1) NOT NULL,
FirstName       NVARCHAR(50) NOT NULL,
LastName       NVARCHAR(50) NOT NULL,
Gender           CHAR(1) NOT NULL,
Address          NVARCHAR(50) NOT NULL,
City                 NVARCHAR(50) NOT NULL,
ZipCode          CHAR(15) NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL,
SSN                 CHAR(15) NOT NULL,
email               VARCHAR(50) NULL

);

Expressions used:

  • Firstname: (Pete|Tom|Mary|Larry|Lisa|Brain|David|Jeff|Amy)
  • LastName: (Johnson|Smith|Good|Anderson|Baker|Crawford)
  • Gender: F|M
  • Address: [1-6]{1}[0-9]{1,3} (SE|NE|NW|SW) [1-2]{1}[0-9]{1,2}th (ST|CT|PL|AVE)
  • City: Seattle|(New York)|Boston|Miami|Beijing|(Los Angles)|London|Paris
  • ZipCode: WA 98[0-9]{3,3}-[1-9]{1,1}[0-9]{3,3}
  • PhoneNumber: \+1 (425|206)-[1-9][0-9]{2,2}-[0-9]{4,4}
  • SSN: [1-9][0-9]{2}-[0-9]{2}-[0-9]{4}
  • email: [a-z]{5,8}@(hotmail\.com|msn\.com|[a-z]{3,8}\.(com|net|org))

The preview result: