-
Notifications
You must be signed in to change notification settings - Fork 0
/
regex_mysql_data.txt
77 lines (69 loc) · 6.23 KB
/
regex_mysql_data.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
### REGEX TYPE MATCH LIST
### FORMAT: TYPE , MAX LENGTH , REGEX MATCH
### NOTE: * at the beginning means this type can be a primary key, otherwise other types are ignored when doing a detect primary key.
### NOTE: + following the * means this primary key can also have the MySQL tag AUTO_INCREMENT
### NOTE: (M_D) for decimal types will be replaced by (M,D) where M = Number of total digits, and D = decimal points
### NOTE: _L_ for varchar or similar indicates that with $fit_data_sizes true will adjust the L value to the max found in the record, otherwise L will equal the given MAX LENGTH field in this file.
*+INT,9,^-?[0-9]{1,9}$
*+BIGINT,17,^-?[0-9]{1,17}$
#FLOAT(M_D),10,^-?[0-9\.]{1,23}$
FLOAT(M_D),23,^-?[0-9\.]{1,23}$
DOUBLE(M_D),52,^-?[0-9\.]{1,52}$
## Description: Currency Validation / Matches: $1,000,000.00
DECIMAL(15,2),22,^-?[\$\£\€\¥]-?(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$
## match percent %
DECIMAL(15,2),22,/^-?[0-9]*\.?([0-9]{2})?%?$/
## 1:45 am | 12:01 aM | 01:23 pm
TIME,12,^(0?[1-9]|1[012])(:[0-5]\d) [AP]M$
## 09:05 | 10:00 | 1:15 | 00:59:00 | 23:01:59
TIME,12,^([0-1][0-9]|[2][0-3]|[0-9]):([0-5][0-9])(?::([0-5][0-9]))?$
## 1:59|01:59|23:59
TIME,12,^([0-1]?[0-9]{1}|2[0-3]{1}):([0-5]{1}[0-9]{1})$
## 1:01:01 | |01:01:01 | |12:23:33: | |22:09:59
TIME,12,^([0]?\d|1\d|2[0-3]):([0-5]\d):([0-5]\d)$
## 2002-11-03 | 2007-17-08 | 9999-99-99
DATE,22,^([0-9]{4})-([0-9]{1,2})-([0-9]{1,2})$
## 2002-11-03 | 2007-17-08 | 9999-99-99
DATE,22,^([0-9]{4})-([0-9]{1,2})-([0-9]{1,2})$
## 12jan | 23dec
DATE,22,^[0-3]{1}[0-9]{1}(jan|JAN|feb|FEB|mar|MAR|apr|APR|may|MAY|jun|JUN|jul|JUL|aug|AUG|sep|SEP|oct|OCT|nov|NOV|dec|DEC){1}$
## 05/15/2008,02/29/2008
DATE,22,^(((0[13578]|10|12)([/])(0[1-9]|[12][0-9]|3[01])([/])([1-2][0,9][0-9][0-9]))|((0[469]|11)([/])([0][1-9]|[12][0-9]|30)([/])([1-2][0,9][0-9][0-9]))|((02)([/])(0[1-9]|1[0-9]|2[0-8])([/])([1-2][0,9][0-9][0-9]))|((02)([/])(29)(\.|-|\/)([02468][048]00))|((02)([/])(29)([/])([13579][26]00))|((02)([/])(29)([/])([0-9][0-9][0][48]))|((02)([/])(29)([/])([0-9][0-9][2468][048]))|((02)([/])(29)([/])([0-9][0-9][13579][26])))$
## 12/25/2007 | 11/07/1517 | 02/25/2727
DATE,22,^((0[1-9])|(1[0-2]))\/((0[1-9])|(1[0-9])|(2[0-9])|(3[0-1]))\/(\d{4})$
## 25-Nov-2005, 25-dEc-2006, 18-2004
DATE,22,^((31(?!(\-)((F|f)(E|e)(B|b)|(A|a)(P|p)(R|r)|(J|j)(U|u)(N|n)|(S|s)(E|e)(P|p)|(N|n)(O|o)(V|v))))|((30|29)(?!(\-)((F|f)(E|e)(B|b))))|(29(?=(\-)(F|f)(E|e)(B|b)(\-)(((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00)))))|(0?[1-9])|1\d|2[0-8])(\-)((J|j)(A|a)(N|n)|(F|f)(E|e)(B|b)|(M|m)(A|a)(R|r)|((M|m)(A|a)(Y|y))|(A|a)(P|p)(R|r)|(J|j)(U|u)(L|l)|(J|j)(U|u)(N|n)|(A|a)(U|u)(G|g)|(O|o)(C|c)(T|t)|(S|s)(E|e)(P|p)|(N|n)(O|o)(V|v)|(D|d)(E|e)(C|c))(\-)((1[6-9]|[2-9]\d)\d{2})$
## 11/jun/1980, 11/JUN/1980, 11/Jun/1980
DATE,22,^(3[0-1]|2[0-9]|1[0-9]|0[1-9])[\/](Jan|JAN|Feb|FEB|Mar|MAR|Apr|APR|May|MAY|Jun|JUN|Jul|JUL|Aug|AUG|Sep|SEP|Oct|OCT|Nov|NOV|Dec|DEC)[\/]\d{4}$
## 01.1.02 | 11-30-2001 | 2/29/2000
DATE,22,^(?:(?:(?:0?[13578]|1[02])(\/|-|\.)31)\1|(?:(?:0?[13-9]|1[0-2])(\/|-|\.)(?:29|30)\2))(?:(?:1[6-9]|[2-9]\d)?\d{2})$|^(?:0?2(\/|-|\.)29\3(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00))))$|^(?:(?:0?[1-9])|(?:1[0-2]))(\/|-|\.)(?:0?[1-9]|1\d|2[0-8])\4(?:(?:1[6-9]|[2-9]\d)?\d{2})$
## 04/2/29 | 2002-4-30 | 02.10.31
DATE,22,^(?:(?:(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00)))(\/|-|\.)(?:0?2\1(?:29)))|(?:(?:(?:1[6-9]|[2-9]\d)?\d{2})(\/|-|\.)(?:(?:(?:0?[13578]|1[02])\2(?:31))|(?:(?:0?[1,3-9]|1[0-2])\2(29|30))|(?:(?:0?[1-9])|(?:1[0-2]))\2(?:0?[1-9]|1\d|2[0-8]))))$
## Jan 1, 2003 | February 29, 2004 | November 02, 3202
DATE,22,^(?:(((Jan(uary)?|Ma(r(ch)?|y)|Jul(y)?|Aug(ust)?|Oct(ober)?|Dec(ember)?)\ 31)|((Jan(uary)?|Ma(r(ch)?|y)|Apr(il)?|Ju((ly?)|(ne?))|Aug(ust)?|Oct(ober)?|(Sept|Nov|Dec)(ember)?)\ (0?[1-9]|([12]\d)|30))|(Feb(ruary)?\ (0?[1-9]|1\d|2[0-8]|(29(?=,\ ((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00)))))))\,\ ((1[6-9]|[2-9]\d)\d{2}))$
## 31 January 2003 | 29 March 2004 | 29 Feb 2008
DATE,22,^((31(?!\ (Feb(ruary)?|Apr(il)?|June?|(Sep(?=\b|t)t?|Nov)(ember)?)))|((30|29)(?!\ Feb(ruary)?))|(29(?=\ Feb(ruary)?\ (((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00)))))|(0?[1-9])|1\d|2[0-8])\ (Jan(uary)?|Feb(ruary)?|Ma(r(ch)?|y)|Apr(il)?|Ju((ly?)|(ne?))|Aug(ust)?|Oct(ober)?|(Sep(?=\b|t)t?|Nov|Dec)(ember)?)\ ((1[6-9]|[2-9]\d)\d{2})$
## Typically for CC Validation dates
## 01/08 | 01-08 | 01.08 | 12/10 | 12/19
DATE,22,^((0[1-9])|(1[0-2]))[\/\.\-]((0[8-9])|(1[1-9]))$
## 2004-07-12 14:25:59 | 1900-01-01 00:00:00 | 9999-12-31 23:59:59
DATETIME,22,^(19[0-9]{2}|[2-9][0-9]{3})-((0(1|3|5|7|8)|10|12)-(0[1-9]|1[0-9]|2[0-9]|3[0-1])|(0(4|6|9)|11)-(0[1-9]|1[0-9]|2[0-9]|30)|(02)-(0[1-9]|1[0-9]|2[0-9]))\x20(0[0-9]|1[0-9]|2[0-3])(:[0-5][0-9]){2}$
## 2006-04-06T12:31:39.6230000-05:00
DATETIME,22,^(?<Date>(?<Year>\d{4})-(?<Month>\d{2})-(?<Day>\d{2}))(?:T(?<Time>(?<SimpleTime>(?<Hour>\d{2}):(?<Minute>\d{2})(?::(?<Second>\d{2}))?)?(?:\.(?<FractionalSecond>\d{1,7}))?(?<Offset>-\d{2}\:\d{2})?))?$
## 2006-12-31 17:58:00 | 20061231175800 | 2006-12-31
DATETIME,22,^(\d{4})\D?(0[1-9]|1[0-2])\D?([12]\d|0[1-9]|3[01])(\D?([01]\d|2[0-3])\D?([0-5]\d)\D?([0-5]\d)?)?$
## 2007-01-01 00:00
DATETIME,22,^[0-9][0-9][0-9][0-9]-(0[1-9]|1[0-2])-(0[1-9]|1[0-9]|2[0-9]|3[0-1])\s{1}(0[0-9]|1[0-9]|2[0-3]):([0-5][0-9])$
## dd/mm/yyyy HH:MM format, 10/10/2010 05:30
DATETIME,22,^((\(\d{2}\) ?)|(\d{2}/))?\d{2}/\d{4} ([0-2][0-9]\:[0-6][0-9])$
## 01-01-2014 10:50 | 01/01/2014 10:50:59 | 11-10-2012
DATETIME,22,^(([0][1-9]|[1|2][0-9]|[3][0|1])[./-]([0][1-9]|[1][0-2])[./-]([0-9]{4})|(([0][1-9]|[1|2][0-9]|[3][0|1])[./-]([0][1-9]|[1][0-2])[./-]([0-9]{4}))[ ]([0|1][0-2])[:](([0-5][0-9]))([:](([0-5][0-9])))?)$
## 2009-04-20 14:34:32 | 2010-03-09 12:59:00 | 1020-03-09 23:59:00
DATETIME,22,^[1-9]{1}[0-9]{3}-(0[1-9]{1}|1[0-2]{1})-([0-2]{1}[1-9]{1}|3[0-1]{1}) ([0-1]{1}[0-9]{1}|2[0-3]{1}):[0-5]{1}[0-9]{1}:[0-5]{1}[0-9]{1}$
## MON NOV 16 16:06:02 2009
DATETIME,22,^(\s(SUN|MON|TUE|WED|THU|FRI|SAT)\s+(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)\s+(0?[1-9]|[1-2][0-9]|3[01])\s+(2[0-3]|[0-1][0-9]):([0-5][0-9]):((60|[0-5][0-9]))\s+(19[0-9]{2}|[2-9][0-9]{3}|[0-9]{2}))$
*VARCHAR(_L_),255,
TEXT,65535,
MEDIUMTEXT,16777215,
LONGTEXT,4294967295,
ERROR,,