SQL Server/T-SQL/String Functions/Regular Expressions
Содержание
Any of the wildcard characters (%, _ [, ], or ?) enclosed in square brackets stand for themselves
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>
Begin with a character in the range C through F
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>
"%" is for any length of letters
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>
"_" is for one letter
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>
^ specifies the negation of a range or a list of characters
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>