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

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Reference » Using SQL » SQL language elements » Expressions » Regular expressions

 

Regular expression examples

The following table shows example uses of regular expressions. Some examples work with both SIMILAR TO and REGEXP, and some work only with REGEXP. Also, some return different results depending on the case-sensitivity of the database.

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

Example Sample matches
Credit Card Numbers:

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:

([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:

[:alpha:]\\[[:graph:][:space:]^:*?"<>|]*

Matches: c:\directory1\directory2

Non-Matches: \directory\directory2, /directory2

Email Addresses:

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

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

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

Email Addresses:

.+@.+\..+

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

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

HTML Hexadecimal Color Codes:

[A-F0-9]{6}

Matches: AB1234, CCCCCC, 12AF3B

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

HTML Hexadecimal Color Codes:

[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:

(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:

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

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

Non-Matches: a=1

Money:

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

Matches: $1.00, -$97.65

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

Numbers (positive, negative, decimal values):

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

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

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

Passwords:

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

Matches: abcd, 1234, A1b2C3d4, 1a2B3

Non-Matches: abc, *ab12, abcdefghijkl

Passwords:

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

Matches: AB_cd, A1_b2c3, a123_

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

Phone Numbers:

([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:

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

Matches: Hello, how are you?

Non-Matches: i am fine

Sentences:

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

Matches: Hello, how are you?

Non-Matches: i am fine

Social Security Numbers:

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

Matches: 123-45-6789

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

URLs:

(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