SQL LIKE 연산자

1 개요

SQL LIKE Operator

SQL LIKE 연산자

• 와일드카드 사용하여 비교 가능

_(밑줄문자)는 임의의 문자

%(퍼센트기호)는 임의의 문자열

• 컬럼 내용이 특정 패턴에 맞는지 필터링
• 값의 일부가 일치하는 경우를 검색하는데 사용

sql

Copy

SELECT * FROM 테이블명 WHERE 컬럼명 LIKE 패턴;

2 실습 1: 와일드카드 없는 경우

• code 컬럼값이 kr인 행

(등호와 같음)

Console

Copy

mysql> SELECT * FROM ip2nationCountries WHERE code LIKE 'kr';
+------+------------+------------+----------------------------+---------------+-----+-------+
| code | iso_code_2 | iso_code_3 | iso_country                | country       | lat | lon   |
+------+------------+------------+----------------------------+---------------+-----+-------+
| kr   | KO         | KOR        | Korea, Republic of (South) | Korea (South) |  37 | 127.3 |
+------+------------+------------+----------------------------+---------------+-----+-------+
1 row in set (0.00 sec)

3 실습 2: 퍼센트 기호

• country 컬럼값이 United로 시작하는 행

Console

Copy

mysql> SELECT * FROM ip2nationCountries WHERE country LIKE 'United%';
+------+------------+------------+----------------------+----------------------+-----+-----+
| code | iso_code_2 | iso_code_3 | iso_country          | country              | lat | lon |
+------+------------+------------+----------------------+----------------------+-----+-----+
| ae   | AR         | ARE        | United Arab Emirates | United Arab Emirates |  24 |  54 |
| uk   | GB         | GBR        | United Kingdom       | United Kingdom       |  54 |  -2 |
| us   | US         | USA        | United States        | United States        |  38 | -97 |
+------+------------+------------+----------------------+----------------------+-----+-----+
3 rows in set (0.00 sec)

• country 컬럼값에 New가 포함된 행

Console

Copy

mysql> SELECT * FROM ip2nationCountries WHERE country LIKE '%New%';
+------+------------+------------+------------------+------------------------+-------+-------+
| code | iso_code_2 | iso_code_3 | iso_country      | country                | lat   | lon   |
+------+------------+------------+------------------+------------------------+-------+-------+
| nc   | NC         | NCL        | New Caledonia    | New Caledonia          | -21.3 | 165.3 |
| nz   | NZ         | NZL        | New Zealand      | New Zealand (Aotearoa) |   -41 |   174 |
| pg   | PN         | PNG        | Papua New Guinea | Papua New Guinea       |    -6 |   147 |
+------+------------+------------+------------------+------------------------+-------+-------+
3 rows in set (0.00 sec)

4 실습 3: 밑줄문자 사용

• iso_code_3 컬럼값의 첫번째 문자가 C, 세번째 문자가 E인 행

Console

Copy

mysql> SELECT * FROM ip2nationCountries WHERE iso_code_3 LIKE 'C_E';
+------+------------+------------+----------------+----------------+-------+------+
| code | iso_code_2 | iso_code_3 | iso_country    | country        | lat   | lon  |
+------+------------+------------+----------------+----------------+-------+------+
| ch   | CH         | CHE        | Switzerland    | Switzerland    |    47 |    8 |
| cz   | CZ         | CZE        | Czech Republic | Czech Republic | 49.45 | 15.3 |
+------+------------+------------+----------------+----------------+-------+------+
2 rows in set (0.01 sec)

• country 컬럼값이 첫번째 문자는 S이고 세번째 문자는 i이며 a로 끝나는 행

(퍼센트기호, 밑줄문자 조합)

Console

Copy

mysql> SELECT * FROM ip2nationCountries WHERE country LIKE 'S_i%a';
+------+------------+------------+-------------+-------------+-------+--------+
| code | iso_code_2 | iso_code_3 | iso_country | country     | lat   | lon    |
+------+------------+------------+-------------+-------------+-------+--------+
| lc   | LC         | LCA        | Saint Lucia | Saint Lucia | 13.53 | -60.68 |
| lk   | LK         | LKA        | Sri Lanka   | Sri Lanka   |     7 |     81 |
+------+------------+------------+-------------+-------------+-------+--------+
2 rows in set (0.00 sec)

5 같이 보기

• SQL 논리 연산자
• SQL 와일드카드
• WHERE 절

6 참고

• http://www.w3schools.com/sql/sql_like.asp