SQL Server/T-SQL/String Functions/Regular Expressions
Содержание
Any of the wildcard characters (%, _ [, ], or ?) enclosed in square brackets stand for themselves
<source lang="sql">
1> CREATE TABLE project (project_no CHAR(4) NOT NULL, 2> project_name CHAR(15) NOT NULL, 3> budget FLOAT NULL) 4> GO 1> insert into project values ("p1", "Search Engine", 120000.00) 2> insert into project values ("p2", "Programming", 95000.00) 3> insert into project values ("p3", "SQL", 186500.00) 4> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> -- Any of the wildcard characters (%, _ [, ], or ?) enclosed in square brackets stand for themselves. 3> 4> SELECT project_no, project_name 5> FROM project 6> WHERE project_name LIKE "%[_]%" 7> GO project_no project_name
---------------
(0 rows affected) 1> drop table project 2> GO 1>
</source>
Begin with a character in the range C through F
<source lang="sql">
1> CREATE TABLE department(dept_no CHAR(4) NOT NULL, 2> dept_name CHAR(25) NOT NULL, 3> location CHAR(30) NULL) 4> GO 1> insert into department values ("d1", "developer", "Dallas") 2> insert into department values ("d2", "tester", "Seattle") 3> insert into department values ("d3", "marketing", "Dallas") 4> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> -- Begin with a character in the range C through F. 2> 3> SELECT * FROM department WHERE location LIKE "[C-F]%" 4> GO dept_no dept_name location
------------------------- ------------------------------
d1 developer Dallas d3 marketing Dallas (2 rows affected) 1> drop table department 2> GO 1>
</source>
"%" is for any length of letters
<source lang="sql">
1> CREATE TABLE employee (emp_no INTEGER NOT NULL, 2> emp_fname CHAR(20) NOT NULL, 3> emp_lname CHAR(20) NOT NULL, 4> dept_no CHAR(4) NULL) 5> GO 1> 2> insert into employee values(1, "Matthew", "Smith", "d3") 3> insert into employee values(2, "Ann", "Jones", "d3") 4> insert into employee values(3, "John", "Barrimore","d1") 5> insert into employee values(4, "James", "James", "d2") 6> insert into employee values(5, "Elsa", "Bertoni", "d2") 7> insert into employee values(6, "Elke", "Hansel", "d2") 8> insert into employee values(7, "Sybill", "Moser", "d1") 9> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> select * from employee 3> GO emp_no emp_fname emp_lname dept_no
-------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected) 1> 2> 3> -- LIKE Operator 4> 5> SELECT emp_fname, emp_lname, emp_no FROM employee WHERE emp_lname LIKE "J%" 6> GO emp_fname emp_lname emp_no
-------------------- -----------
Ann Jones 2 James James 4 (2 rows affected) 1> drop table employee 2> GO 1>
</source>
"_" is for one letter
<source lang="sql">
1> CREATE TABLE employee (emp_no INTEGER NOT NULL, 2> emp_fname CHAR(20) NOT NULL, 3> emp_lname CHAR(20) NOT NULL, 4> dept_no CHAR(4) NULL) 5> GO 1> insert into employee values(1, "Matthew", "Smith", "d3") 2> insert into employee values(2, "Ann", "Jones", "d3") 3> insert into employee values(3, "John", "Barrimore","d1") 4> insert into employee values(4, "James", "James", "d2") 5> insert into employee values(5, "Elsa", "Bertoni", "d2") 6> insert into employee values(6, "Elke", "Hansel", "d2") 7> insert into employee values(7, "Sybill", "Moser", "d1") 8> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> select * from employee 2> GO emp_no emp_fname emp_lname dept_no
-------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected) 1> 2> SELECT emp_fname, emp_lname, emp_no FROM employee WHERE emp_fname LIKE "_a%" 3> GO emp_fname emp_lname emp_no
-------------------- -----------
Matthew Smith 1 James James 4 (2 rows affected) 1> drop table employee 2> GO 1>
</source>
^ specifies the negation of a range or a list of characters
<source lang="sql">
1> CREATE TABLE employee (emp_no INTEGER NOT NULL, 2> emp_fname CHAR(20) NOT NULL, 3> emp_lname CHAR(20) NOT NULL, 4> dept_no CHAR(4) NULL) 5> GO 1> insert into employee values(1, "Matthew", "Smith", "d3") 2> insert into employee values(2, "Ann", "Jones", "d3") 3> insert into employee values(3, "John", "Barrimore","d1") 4> insert into employee values(4, "James", "James", "d2") 5> insert into employee values(5, "Elsa", "Bertoni", "d2") 6> insert into employee values(6, "Elke", "Hansel", "d2") 7> insert into employee values(7, "Sybill", "Moser", "d1") 8> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> select * from employee 2> GO emp_no emp_fname emp_lname dept_no
-------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected) 1> -- ^ specifies the negation of a range or a list of characters 2> 3> SELECT emp_no, emp_fname, emp_lname FROM employee 4> WHERE emp_lname LIKE "[^J-O]%" 5> AND emp_fname LIKE "[^EZ]%" 6> GO emp_no emp_fname emp_lname
-------------------- --------------------
1 Matthew Smith
3 John Barrimore
(2 rows affected) 1> drop table employee 2> GO 1> 2>
</source>
