SQLで文字列を扱う方法についてまとめています。
MySQLのサンプルデータベースEmployeesを使用しています。
SQLの文字列操作の方法まとめ 検索、部分一致、置換、抽出
SQLで文字列の検索をするには、like
likeにワイルドカード指定することで、SQLで文字列の検索が可能です。
mysql> select emp_no, birth_date, first_name, last_name from employees where first_name like "M%" limit 10; +--------+------------+------------+------------+ | emp_no | birth_date | first_name | last_name | +--------+------------+------------+------------+ | 10011 | 1953-11-07 | Mary | Sluis | | 10020 | 1952-12-24 | Mayuko | Warwick | | 10042 | 1956-02-26 | Magy | Stamatiou | | 10044 | 1961-09-21 | Mingsen | Casley | | 10045 | 1957-08-14 | Moss | Shanbhogue | | 10054 | 1957-04-04 | Mayumi | Schueller | | 10069 | 1960-09-06 | Margareta | Bierman | | 10074 | 1955-08-28 | Mokhtar | Bernatsky | | 10077 | 1964-04-18 | Mona | Azuma | | 10109 | 1958-11-25 | Mariusz | Prampolini | +--------+------------+------------+------------+ 10 rows in set (0.00 sec)
【関連記事】
▶SQLの文字列検索 likeとワイルドカードを使って、あいまい検索が可能
文字列の部分一致
likeのワイルドカードを工夫することで、文字列の部分一致が可能です。以下のサンプルは、employees（社員）テーブルから、first_name（姓名の名）の中にraが入っているものを抽出しています。ただし、文字列の先頭がraのものと、文字列の最後尾がraのデータを除きます。
mysql> SELECT * FROM employees WHERE first_name like '_%ra%_'; +--------+------------+---------------+----------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+---------------+----------------+--------+------------+ | 10025 | 1958-10-31 | Prasadram | Heyers | M | 1987-08-17 | | 10037 | 1963-07-22 | Pradeep | Makrucki | M | 1990-12-05 | | 10102 | 1959-11-04 | Paraskevi | Luby | F | 1994-01-26 | | 10124 | 1962-05-23 | Geraldo | Marwedel | M | 1991-09-05 | | 10168 | 1964-09-11 | Dharmaraja | Stassinopoulos | M | 1986-12-06 | | 10177 | 1954-11-08 | Pragnesh | Iisaka | M | 1993-02-06 | | 10198 | 1953-05-28 | Pranav | Furedi | M | 1985-08-31 | | 10250 | 1958-08-12 | Serap | Etalle | M | 1992-08-30 | | 10297 | 1956-06-17 | Narain | Oaver | F | 1986-05-28 | | 10347 | 1953-10-03 | Pradeep | Kaminger | M | 1985-12-12 |
【関連記事】
▶SQLの部分一致にはlikeとワイルドカードを指定 中間一致指定も可能
文字列の置換
文字列のち感はreplace関数を使うことで可能です。置換文字列が複数ある場合は、case式を組み合わせると、すっきり見やすく記述できます。
以下のサンプルコードは、employees（社員）テーブルのgender（性別）がMの場合はDansei、Fの場合はJoseiと表示します。
mysql> select first_name, last_name,
-> case
-> when gender like '%M%' then replace(gender, 'M', 'Dansei')
-> when gender like '%F%' then replace(gender, 'F', 'Josei')
-> end as seibetu
-> from employees limit 10;
+------------+-----------+---------+
| first_name | last_name | seibetu |
+------------+-----------+---------+
| xGeorgi | Facello | Dansei |
| Bezalel | Simmel | Josei |
| Parto | Bamford | Dansei |
| Chirstian | Koblick | Dansei |
| Kyoichi | Maliniak | Dansei |
| Anneke | Preusig | Josei |
| Tzvetan | Zielinski | Josei |
| Saniya | Kalloufi | Dansei |
| Sumant | Peac | Josei |
| Duangkaew | Piveteau | Josei |
+------------+-----------+---------+
10 rows in set (0.00 sec)
【関連記事】
▶SQLで文字列の置換をするにはreplace関数使用。 複数置換をするサンプルコード
文字列の抽出
substringを使って、指定開始位置から、指定文字数分の文字列を抽出できます。
以下のサンプルコードは、メールアドレスのドメイン部分のみを抽出します。username@domain.comの形式のデータがemailカラムに入っている前提です。
select email, substring(email, instr(email, '@')+1 ) from emaillist;
【関連記事】
▶SQLの文字列抽出には、substring等を使用。メルアドのドメイン名を抽出する方法
まとめ
ポテパンダの一言メモ
- SQLの文字列検索には、likeを使う
- SQLの部分一致は、likeとワイルドカードの組み合わせで実現可能
- SQLの置換は、replace関数。case式と組み合わせると簡潔に記述可能。
- SQLの抽出は、right、left、substringなど。指定文字出現位置からの抽出も可能。
