LIKE 구문

LIKE 구문은 쿼리문 WHERE절에 주로 사용되며 부분적으로 일치하는 칼럼을 찾을때 사용합니다.

--A로 시작하는 문자를 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE 'A%'

--A로 끝나는 문자 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '%A'

--A를 포함하는 문자 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '%A%'

--A로 시작하는 두글자 문자 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE 'A_'

--첫번째 문자가 'A''가 아닌 모든 문자열 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE'[^A]'

--첫번째 문자가 'A'또는'B'또는'C'인 문자열 찾기--
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[ABC]'
SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[A-C]'

예시

Find the country that start with Y

SELECT name FROM world
  WHERE name LIKE 'Y%'

	name
	Yemen

Find the countries that end with y

SELECT name FROM world
  WHERE name LIKE '%y'

  name
  Germany
  Hungary
  Italy
  Norway
  Paraguay
  Turkey
  Uruguay
  Vatican City

Find the countries that contain the letter x

SELECT name FROM world
  WHERE name LIKE '%x%'

  name
  Luxembourg
  Mexico

Find the countries that end with land

SELECT name FROM world
  WHERE name LIKE '%land'

  name
  Finland
  Iceland
  Ireland
  New Zealand
  Poland
  Swaziland
  Switzerland
  Thailand

Find the countries that start with C and end with ia

SELECT name FROM world
  WHERE name LIKE 'C%ia'

  name
  Cambodia
  Colombia
  Croatia

Find the country that has oo in the name

SELECT name FROM world
  WHERE name LIKE '%oo%'

  name
  Cameroon

Find the countries that have three or more a in the name

SELECT name FROM world
  WHERE name LIKE '%a%a%a%'

  name
  Afghanistan
  Albania
  Antigua and Barbuda
  Australia
  Azerbaijan
  Bahamas
  Bosnia and Herzegovina
  Canada
  Central African Republic
  Equatorial Guinea
  Guatemala
  Jamaica
  Kazakhstan
  Madagascar
  Malaysia
  Marshall Islands
  Mauritania
  Micronesia, Federated States of
  Nicaragua
  Panama
  Papua New Guinea
  Paraguay
  Saint Vincent and the Grenadines
  Saudi Arabia
  Tanzania
  Trinidad and Tobago
  United Arab Emirates

Find the countries that have "t" as the second character

SELECT name FROM world
 WHERE name LIKE '_t%'
ORDER BY name

  name
  Ethiopia
  Italy

Find the countries that have two "o" characters separated by two others.

SELECT name FROM world
 WHERE name LIKE '%o__o%'

  name
  Congo, Democratic Republic of
  Congo, Republic of
  Lesotho
  Moldova
  Mongolia
  Morocco
  Sao Tomé and Príncipe

Find the countries that have exactly four characters.

SELECT name FROM world
 WHERE name LIKE 'Cu%'

  name
  Chad
  Cuba
  Fiji
  Iran
  Iraq
  Laos
  Mali
  Oman
  Peru
  Togo

Find the country where the name is the capital city.

SELECT name
  FROM world
 WHERE name =capital;

  name
  Djibouti
  Luxembourg
  San Marino
  Singapore

Find the country where the capital is the country plus "City".

SELECT name
  FROM world
 WHERE capital = concat(name,' City');

  name
  Guatemala
  Kuwait
  Mexico
  Panama

Find the capital and the name where the capital includes the name of the country.

SELECT capital,name
FROM world
WHERE capital LIKE CONCAT ('%', name,'%');

  capital	name
  Andorra la Vella	Andorra
  Djibouti	Djibouti
  Guatemala City	Guatemala
  Kuwait City	Kuwait
  Luxembourg	Luxembourg
  Mexico City	Mexico
  Monaco-Ville	Monaco
  Panama City	Panama
  San Marino	San Marino
  Singapore	Singapore

Find the capital and the name where the capital is an extension of name of the country.

SELECT capital, name
FROM world
WHERE capital like CONCAT('%', name, '%') AND capital>name;

  capital	name
  Andorra la Vella	Andorra
  Guatemala City	Guatemala
  Kuwait City	Kuwait
  Mexico City	Mexico
  Monaco-Ville	Monaco
  Panama City	Panama

Show the name and the extension where the capital is an extension of name of the country.

SELECT name, REPLACE( capital, name, '') as'ext'
FROM world 
WHERE capital LIKE concat(name, '_%');

  name	ext
  Andorra	la Vella
  Guatemala	City
  Kuwait	City
  Mexico	City
  Monaco	-Ville
  Panama	City