訓練場(実行環境)の作成と文字列関数

データベース

技術解説

連載 [第1回] :

初心者のための SQL-BOOTCAMP

2022年10月21日(金)

久保司

はじめに

本連載では、SQLを始めた初心者の方々向けに「BOOTCAMP(新兵訓練所)」と題して、解説を進めていきます。

一般的にSQLの言語は、大きく分けて次の3つに分類されます。

データ操作言語(DML：Data Manipulation Language)
データ定義言語(DDL：Data Definition Language)
データ制御言語(DCL：Data Control Language)

この3つのうち、SQL-BOOTCAMPでは、特によく利用されるデータ操作言語(DML)について説明します。ぜひ、実践で役立つ武器(DML)を身に着けていってください。

また、訓練に使用するデータベースは、インストールが簡単な「SQLite」を使用します。解説の中でテーブルやデータ作成のSQLも載せていきますので、皆さんもSQLiteをインストールして実際に試してみてください。

それでは早速、訓練に行ってみましょう！

準備運動

訓練に入る前に、準備運動としてSQLiteで訓練場(データベース)として使用するテーブルとデータを作成してみましょう。SQLiteをインストール済みの状態から説明しますので、皆さんご自身でインストールは済ませておいてください。

コマンドプロンプトを起動して、SQLiteをインストールした「sqlite3.exe」が存在するフォルダに移動し、下記コマンドを入力してデータベースを作成します。
```
sqlite3 データベース名 (sqlbc.dbというデータベースを作成します)
```
コマンドラインに、下記のCREATEコマンドを入力して、「tbl_employees」テーブルを作成します。
```
CREATE TABLE tbl_employees (code INTEGER,name TEXT,kana TEXT,gender TEXT,birthday TEXT,PRIMARY KEY(code));
```
コマンドラインに「.table」と入力して[enter]キーを押下し、作成したテーブルを確認してみましょう。

作成したテーブルに下記のINSERTコマンドでデータを挿入しましょう。コマンドラインにコマンドを入力し、[enter]キーで完了です。

INSERT INTO tbl_employees (code,name,kana,gender,birthday) 
VALUES
('1',' HORII　SEITARO ','ﾎﾘｲｾｲﾀﾛｳ','M', '1970-12-27')
,('2','ISHIMURA　HANA','ｲｼﾑﾗﾊﾅ', 'F','1981-08-24')
,('3','KITAJIMA RUNA','ｷﾀｼﾞﾏﾙﾅ','F', '1981-11-09')
,('4','MUROI YURI','ﾑﾛｲﾕﾘ','F', '1982-02-12')
,('5','KITAZAWA AIKA','ｷﾀｻﾞﾜｱｲｶ','F', '1980-08-05')
,('6','MITANI KOUKITI','ﾐﾀﾆｺｳｷﾁ','M', '1979-03-16');

コマンドラインに「SELECT * FROM tbl_employees;」と入力して[enter]キーを押下すると、先ほど挿入したデータの一覧が表示されます。

SQLiteのコマンドを入力して見やすくしてみましょう。
```
.headers on　　 -- ヘッダを表示します
.mode column  -- 項目の幅をそろえて表示します

SELECT * FROM tbl_employees;
```

これで、訓練場(データベース)の完成です。

補足ですが、データベースの接続を解除する場合は「.exit」コマンドを使用します。また、再度接続する場合は、データベースを作成したコマンドと同様に「sqlite3 データベース名」と入力します(データベース名に指定したファイルが存在しない場合は新規にデータベースが作成され、存在する場合は接続されるようになっています)。

便利な武器その1：文字列関数

文字列関数とは、文字列の項目に対して文字操作を行う関数です。文字列を連結したり、文字列から指定した文字を削除したり、文字を切り出したりと様々な関数が存在します。また、例えば切り出した文字列を変換する等、複数の関数を組み合わせて使用することもできます。

ただし、数値項目には使用できません。その場合は、数値項目を文字列項目に変換する必要があるので注意しましょう。

今回は、次の3つの文字列関数を紹介します。

(1)TRIM関数

先ほど作成した社員テーブル(tbl_employees)に登録されているデータを使用して説明します。見た目ではわかりませんが、下図の「登録されているデータ」のように、code=1のnameの前後には半角ブランクが設定されています。

TRIM、RTRIM、LRTIM関数を使用すると、「使用例」のようにそれぞれ文字が取り除かれた結果が取得されます。

それでは、実際にSQLを入力して試してみましょう。1件のみ表示するため、WHERE句でcode=1の条件を付与しています。

SELECT TRIM ( name ) FROM tbl_employees WHERE code = 1;

SELECT RTRIM ( name ) FROM tbl_employees WHERE code = 1;

SELECT LTRIM ( name ) FROM tbl_employees WHERE code = 1;

SELECT TRIM (TRIM ( name ) , 'RO' ) FROM tbl_employees WHERE code = 1;

4番目のSQL文は、1つのSQLでTRIM関数を2回使用して文字を取り除いています。図の使用例はSELECT文ですが、UPDATE文でも同様に使用できるので、入力時に余計な空白等が入ってしまったデータを整形する場合に便利な関数です。

(2)REPLACE関数

TRIM関数と同様に、社員テーブル(tbl_employees)に登録したデータを使用して説明します。見た目では分かりにくいですが、code=2の姓と名の間には全角ブランクが設定されています。

実際には、下図の「登録されているデータ」のようになっています。

REPLACE関数を使用すると、図の使用例のように文字列が置換された結果を取得できます。

1つ目のSQLでは、全角ブランクを空文字に置き換えています。
2つ目のSQLでは「HA」の文字列を「KA」に置き換えています。
そして、3つ目のSQLではREPLACE関数を2度使用して、1つ目と2つ目のSQLを1度で同時に実行しています。

それでは、実際に入力して試してみましょう。

SELECT REPLACE ( name , '　' ,'') FROM tbl_employees WHERE code = 2;

SELECT REPLACE ( name ,'HA','KA') FROM tbl_employees WHERE code = 2;

SELECT REPLACE ( REPLACE ( name , '　' ,'' ) ,'HA','KA') 
FROM tbl_employees WHERE code = 2;

REPLACE関数はUPDATE文でも使用できるので、文字列の一括変換等に便利です。

(3)SUBSTR関数

これも、社員テーブル(tbl_employees)に登録したデータを使用して説明します。下図に「登録されているデータ」を切り出すパターンを変えて実施した結果を掲載します。

それでは、実際に入力して試してみましょう。

SELECT code, SUBSTR ( name , 1 , 7 ) FROM tbl_employees WHERE code = 3;

SELECT code, SUBSTR ( name, 9 , 4 ) FROM tbl_employees WHERE code = 3;

SELECT code, SUBSTR ( name, 10 ) FROM tbl_employees WHERE code = 3;

SELECT code, SUBSTR ( name, -4 , 3 ) FROM tbl_employees WHERE code = 3;