Share via

RegEx Is Your Friend

  • Article

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:

Comments

  • Anonymous
    November 27, 2006
    IMHO, while the DB Pro SKU has lots to like, one of the features I fell in love with the moment I heard

  • Anonymous
    November 29, 2006
    Gert Drapers has a great post on taking full advantage of the regular expression generator. He shows

  • Anonymous
    November 29, 2006
    I came across a good blog entry with a list of commonly used regular expressions . Through it would be

  • Anonymous
    December 01, 2006
    The SRLTeam Blog on Setting TFS Notification Problem. Rob Caron on Orcas Feature Specs Online and...

  • Anonymous
    December 03, 2006
    Thanks to Gert Drapers' blog: http://blogs.msdn.com/gertd/archive/2006/11/26/regex-is-your-friend.aspx

  • Anonymous
    June 25, 2007
    I recently wrote a short article as an overview to why regular expressions should be in every developer's

  • Anonymous
    June 29, 2007
    One way to generate data in DB Dude is to use the Regular Expression generator, but using regular expressions