SQL LIKE 연산자

1 개요[ | ]

SQL LIKE Operator
SQL LIKE 연산자
_(밑줄문자)는 임의의 문자
%(퍼센트기호)는 임의의 문자열
  • 컬럼 내용이 특정 패턴에 맞는지 필터링
  • 값의 일부가 일치하는 경우를 검색하는데 사용
SELECT * FROM 테이블명 WHERE 컬럼명 LIKE 패턴;

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

  • code 컬럼값이 kr인 행
(등호와 같음)
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로 시작하는 행
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가 포함된 행[1]
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인 행
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로 끝나는 행
(퍼센트기호, 밑줄문자 조합)
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 같이 보기[ | ]

6 참고[ | ]

  1. %를 앞에서 쓰는 것은 DB에 상당한 부하가 되므로 자제할 필요가 있음
문서 댓글 ({{ doc_comments.length }})
{{ comment.name }} {{ comment.created | snstime }}