Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Reference » SQL language elements » Expressions

 

Regular expression examples

The following table shows example uses of regular expressions. All examples work for REGEXP and some also work for SIMILAR TO, as noted in the Example column. Results vary depending on the search condition you use for searching. For those that work with SIMILAR TO, results can vary further depending on case and accent sensitivity.

For a comparison of how REGEXP and SIMILAR TO handle matches and evaluate ranges, see LIKE, REGEXP, and SIMILAR TO search conditions.

Note that backslashes should be doubled if the examples are used in literal strings (for example, '.+@.+\\..+')

Example Sample matches

Credit Card Numbers (REGEXP only):

Visa:

4[0-9]{3}\s[0-9]{4}\s[0-9]{4}\s[0-9]{4}

MasterCard:

5[0-9]{3}\s[0-9]{4}\s[0-9]{4}\s[0-9]{4}

American Express:

37[0-9]{2}\s[0-9]{4}\s[0-9]{4}\s[0-9]{4}

Discover:

6011\s[0-9]{4}\s[0-9]{4}\s[0-9]{4}

Matches (Visa): 4123 6453 2222 1746

Non-Matches (Visa):

3124 5675 4400 4567, 4123-6453-2222-1746

Similarly, MasterCard matches a set of 16 numbers, starting with 5, with a space between each subset of four numbers. American Express and Discover are the same, but must start with 37 and 6011 respectively.

Dates (REGEXP and SIMILAR TO):

([0-2][0-9]|30|31)/(0[1-9]|1[0-2])/[0-9]{4}

Matches: 31/04/1999, 15/12/4567

Non-Matches: 31/4/1999, 31/4/99, 1999/04/19, 42/67/25456

Windows absolute paths (REGEXP only):

([A-Za-z]:|\\)\\[[:alnum:][:whitespace:]!"#$%&'()+,-.\\;=@\[\]^_`{}~.]*

Matches: \\server\share\file

Non-Matches: \directory\directory2, /directory2

Email Addresses (REGEXP only):

[[:word:]\-.]+@[[:word:]\-.]+\.[[:alpha:]]{2,3}

Matches: abc.123@def456.com, _123@abc.ca

Non-Matches: abc@dummy, ab*cd@efg.hijkl

Email Addresses (REGEXP only):

.+@.+\..+

Matches: *@qrstuv@wxyz.12345.com, __1234^%@@abc.def.ghijkl

Non-Matches: abc.123.*&ca, ^%abcdefg123

HTML Hexadecimal Color Codes (REGEXP and SIMILAR TO):

[A-F0-9]{6}

Matches: AB1234, CCCCCC, 12AF3B

Non-Matches: 123G45, 12-44-CC

HTML Hexadecimal Color Codes (REGEXP only):

[A-F0-9]{2}\s[A-F0-9]{2}\s[A-F0-9]{2}

Matches: AB 11 00, CC 12 D3

Non-Matches: SS AB CD, AA BB CC DD, 1223AB

IP Addresses (REGEXP only):

((2(5[0-5]|[0-4][0-9])|1([0-9][0-9])|([1-9][0-9])|[0-9])\.){3}(2(5[0-5]|[0-4][0-9])|1([0-9][0-9])|([1-9][0-9])|[0-9])

Matches: 10.25.101.216

Non-Matches: 0.0.0, 256.89.457.02

Java Comments (REGEXP only):

/\*.*\*/|//[^\n]*

Matches Java comments that are between /* and */, or one line comments prefaced by //.

Non-Matches: a=1

Money (REGEXP only):

(\+|-)?\$[0-9]*\.[0-9]{2}

Matches: $1.00, -$97.65

Non-Matches: $1, 1.00$, $-75.17

Positive, negative numbers, and decimal values (REGEXP only):

(\+|-)?[0-9]+(\.[0-9]+)?

Matches: +41, -412, 2, 7968412, 41, +41.1, -3.141592653

Non-Matches: ++41, 41.1.19, -+97.14

Passwords (REGEXP and SIMILAR TO):

[[:alnum:]]{4,10}

Matches: abcd, 1234, A1b2C3d4, 1a2B3

Non-Matches: abc, *ab12, abcdefghijkl

Passwords (REGEXP only):

[a-zA-Z]\w{3,7}

Matches: AB_cd, A1_b2c3, a123_

Non-Matches: *&^g, abc, 1bcd

Phone Numbers (REGEXP and SIMILAR TO):

([2-9][0-9]{2}-[2-9][0-9]{2}-[0-9]{4})|([2-9][0-9]{2}\s[2-9][0-9]{2}\s[0-9]{4})

Matches: 519-883-6898, 519 888 6898

Non-Matches: 888 6898, 5198886898, 519 883-6898

Sentences (REGEXP only):

[A-Z0-9].*(\.|\?|!)

Matches: Hello, how are you?

Non-Matches: i am fine

Sentences (REGEXP only):

[[:upper:]0-9].*[.?!]

Matches: Hello, how are you?

Non-Matches: i am fine

Social Security Numbers (REGEXP and SIMILAR TO):

[0-9]{3}-[0-9]{2}-[0-9]{4}

Matches: 123-45-6789

Non-Matches:123 45 6789, 123456789, 1234-56-7891

URLs (REGEXP only):

(http://)?www\.[a-zA-Z0-9]+\.[a-zA-Z]{2,3}

Matches: http://www.sample.com, www.sample.com

Non-Matches: http://sample.com, http://www.sample.comm

 See also