loading..
   English
18:30

LIKE predicate page 2

LIKE predicate and regular expressions

LIKE predicate in its standard edition does not support regular expressions, though a number of implementations (in particular, Oracle) supposes their usage, expanding the standard.

In  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2005/2008, the use of regular expressions is possible through CLR, i.e. by means of Visual Studio languages which can be used for writing stored procedures and UDFs.

However, Transact-SQL, besides standard wildcard characters ("%" and "_"), introduces pair of additional symbols which make LIKE predicate more flexible as a tool. These symbols are:

  • [ ] - a single symbol from a character set (for example, [zxy]) or a range ([a-z]) specified in square brackets. Thus, it is possible to list multiple ranges at once (for example, [0-9a-z]);
  • ^ - which in combination with square brackets excludes from the search sample the characters listed in a set or a range.

Let's explain use of these symbols by examples.

Console
Execute
  1. SELECT * FROM
  2. (
  3. SELECT '5%' name UNION ALL
  4. SELECT '55' UNION ALL
  5. SELECT '5%%' UNION ALL
  6. SELECT '3%%' UNION ALL
  7. SELECT 'a5%%' UNION ALL
  8. SELECT 'abc' UNION ALL
  9. SELECT 'abc 5% cde' UNION ALL
  10. SELECT '5c2e' UNION ALL
  11. SELECT 'C2H5OH' UNION ALL
  12. SELECT 'C25OH' UNION ALL
  13. SELECT 'C54OH'
  14. ) x
  15. /* 1 */
  16. --WHERE name LIKE'5%' -- begins with 5
  17. /* 2 */
  18. --WHERE name LIKE '5[%]' -- 5%
  19. /* 3 */
  20. --WHERE name LIKE '5|%' ESCAPE '|'-- 5%
  21. /* 4 */
  22. --WHERE name LIKE '%5|%%' ESCAPE '|' -- 5% in any place of a string
  23. /* 5 */
  24. --WHERE name LIKE '[0-9][a-zA-Z]%' -- the first is a digit, the second is a letter
  25. /* 6 */
  26. --WHERE name LIKE '[a-z][0-9]%' -- the first is a letter, the second is a digit
  27. /* 7 */
  28. --WHERE name LIKE '[^0-9]%' -- begins with not a digit
  29. /* 8 */
  30. --WHERE name LIKE '%[02468]%' -- contains an even digit
  31. /* 9 */
  32. --WHERE name LIKE '%[02468][13579]%' -- a combination even-odd

In the given query some data are generated, for search over which the LIKE predicate is used. Nine examples is accordingly 9 commented (--) WHERE clauses. For check of results of executing of above query on the site, erase preliminary the comment for one of the lines beginning with WHERE. For those who cannot use a site, I shall present the results of executing these examples below.

1. All the strings, which begin with 5:

name
5%
55
5%%
5c2e

2. Searching of the string '5%'. The character in brackets is perceived as a usual single symbol:

name
5%

3.Other solution which is similar to the second one, but it is using the ESCAPE clause, specifying that it is necessary to perceive "%" as an usual symbol.

4. Searching of the substring '5%' being at any place in the string:

name
5%
5%%
a5%%
abc 5% cde

5. Searching of a string in which the first symbol is a digit, but the second one is a letter:

name
5c2e

6. Searching of a string in which the first symbol is a letter, and the second one is a digit. A variant for case-independent comparisons:

name
a5%%
C2H5OH
C25OH
C54OH

7. Searching of a string which begins not with digit:

name
a5%%
abc
abc 5% cde
C2H5OH
C25OH
C54OH

8. Searching of a string which contains even digit:

name
5c2e
C2H5OH
C25OH
C54OH

9. Searching of a string which contains successively going even and odd digits:

name
C25OH

Suggested exercises:35

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
24
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100