SQLを使用してサブストリングを変更する
データベース表でストリング値の一部を変更しなければならない場合があります。SQL は、それを達成できる 3 つのメカニズムを備えています。こうしたメカニズムについて、またその使用方法を知っていますか？
まず、これを例証する目的で表が必要です。
メカニズム1：サブストリングと連結
サブストリング関数と連結を使用して、ストリングの新しい値を構築できます。この方法は、サブストリングが開始する位置を知っている場合に適しています。
例えば、ID が「XX」で始まるパーツがあります。「XX」を「AA」に変えましょう。
文字列「AA」を 3 番目の ID の次の文字に連結し、結果を ID 列 に割り当て戻しました。
その結果、以下のような表になります。
| ID | 説明 |
|---|---|
| AA-101 | 20インチの Doodad (道具) |
| AA-105 | 2フィートの Doodad (道具)左側 |
| AA-106 | 2フィートの Doodad (道具)右側 |
| AA-118 | 12インチの Doodad (道具) |
| BA-101 | 5CM ウィジェット |
| BA-104 | 15Cm ウィジェット |
| BA-111 | 150cm ウィジェット |
| BA-145 | 15cm ウィジェットを使用 |
| CT-201 | ACME チェーン・ストレッチャー |
| CT-202 | 改造された ACME スカイ・フック |
置き換えるストリングおよび置き換えストリングの長さは、同じである必要はありません。この例では、たまたま同じになりました。
メカニズム2：REPLACE関数
サブストリングが開始する位置を知らない場合、REPLACE 関数を使用して、あるストリングを別のストリングに置き換えることができます。粗野な言葉「doodad (道具)」をもっとエレガントで都会的な「doohickey (道具)」という言葉に置き換えましょう。
説明を見てみましょう。
| ID | 説明 |
|---|---|
| AA-101 | 20インチの Doohickey (道具) |
| AA-105 | 2フィートの Doohickey (道具)左側 |
| AA-106 | 2フィートの Doohickey (道具)右側 |
| AA-118 | 12フィートの Doohickey (道具) |
| BA-101 | 5CM ウィジェット |
| BA-104 | 15Cm ウィジェット |
| BA-111 | 150cm ウィジェット |
| BA-145 | 15cm ウィジェットを使用 |
| CT-201 | ACME チェーン・ストレッチャー |
| CT-202 | 改造された ACME スカイ・フック |
WHERE 節は必要ありませんでしたが、それを含めることで SQL が他の行を不用意に更新しないようにしました。
メカニズム3：正規表現
正規表現は不可解ですが、パワフルなワイルドカード処理です。詳しくはMichael Sansoterra's superb explanation をお読みください。
センチメートルで測定されているパーツを 2種類の方法で変えてみましょう。まず、センチメートルの省略形である cm が小文字であることを確認します。次に、cm と次のテキストの間にスペースを 1つだけ入れましょう。次のように更新されます。
では、表を見てみましょう。
| ID | 説明 |
|---|---|
| AA-101 | 20インチの Doohickey (道具) |
| AA-105 | 2フィートの Doohickey (道具)左側 |
| AA-106 | 2フィートの Doohickey (道具)右側 |
| AA-118 | 12フィートの Doohickey (道具) |
| BA-101 | 5cm ウィジェット |
| BA-104 | 15cm ウィジェット |
| BA-111 | 150cm ウィジェット |
| BA-145 | 15cm ウィジェットを使用 |
| CT-201 | ACME チェーン・ストレッチャー |
| CT-202 | 改造された ACME スカイ・フック |
WHERE 節は関数のような正規表現を使用して、数字の直後に大文字小文字に関係なく「CM」が続く行を選択します。もう少し詳しく見てみましょう。
- [0-9] は任意の数字に一致します。
- + 先行一致の 1 回以上の発生に一致することを意味します。これにより ACME パーツは更新用に選択されません。
- [Cc] は、大文字小文字に関係なく文字 C に一致するようシステムに指示します。
- [Mm] は、大文字小文字に関係なく文字 M に一致するようシステムに指示します。
正規表現置き換え関数は、ストリングの一部を変更します。
- ( は最初のキャプチャー・グループの開始を示します。
- [0-9] は任意の数字に一致します。
- + 先行一致の 1 回以上の発生に一致することを意味します。
- ) は最初のキャプチャー・グループの終了を示します。
- [Cc] は、大文字小文字に関係なく文字 C に一致するようシステムに指示します。
- [Mm] は、大文字小文字に関係なく文字 M に一致するようシステムに指示します。
- ( は 2 番目のキャプチャー・グループの開始を示します。
- ブランクは、ブランク文字の検索を指示します。
- + 先行一致の 1 回以上の発生に一致することを意味します。
- ) は 2 番目のキャプチャー・グループの終了を示します。
該当の一致が見つかると、第 3 パラメーターを使用して一致したテキストを置き換えます。
- $1 は、CM という文字が先行する数字で構成されている、最初のキャプチャー・グループの内容を含めることを意味します。
- 文字「cm」とブランク 1 つは、最初のキャプチャー・グループに続くストリングに置き換えられます。
- 「Cm」に続いてブランクがある 2 番目のキャプチャー・グループは参照されないため、置き換えストリングには含まれません。
残りの説明は一致の部分ではないため、影響はありません。
いかがでしょうか。3 つの置き換えメカニズムです。ストリングの一部を置き換える必要があっても大丈夫です。
