mysql substring substring_index functions with examples
MySQLのSUBSTRING関数とSUBSTRING_INDEX関数、およびその使用法について、複数の例を使用して学習します。
MySQL Substring関数は、入力文字列に対して部分文字列または部分文字列を抽出するために使用されます。名前が示すように、Substring関数は文字列入力を操作し、指定されたオプションに対して小さい部分文字列を返します。
また、SUBSTRING_INDEXと呼ばれるSUBSTRINGの別のバリアントについても学習します。この関数は、文字または文字列値を区切り文字として受け入れ、指定された区切り文字値に対して検索を実行します。
また、SELECTクエリの一部としてデータをクエリするときに、これらの関数をどのように使用できるかを理解するために、いくつかの例を見ていきます。
学習内容：
MySQL SUBSTRING
構文：
// format 1 SELECT SUBSTRING('{String Value}', {start_index}, {count}); // format 2 SELECT SUBSTRING('{String Value}'FROM {start_index} for {count}); SUBSTRING関数を使用する方法は2つあります。
例に入る前に、構文を理解しましょう。
- {文字列値}： これは、実際の文字列値、または処理する文字列を含む列名です。
- {start_index}： start_indexは、部分文字列が開始される左または右からのインデックスです。文字列を左側から抽出するか右側から抽出するかに応じて、値は正または負になります。これは、例でより明確になります。
- {カウント}： このフィールドはオプションであり、からの文字数を指定します start_index 部分文字列を抽出する必要があるフィールド。言及されていない場合は、一致から始まる文字列全体 start_index 返されます。
format2はformat1のバリエーションですが、キーワードFROMとFORを使用すると読みやすくなります。したがって、ここでは、FROMは言及する前に使用されます start_index 値、およびFORは言及する前に使用されます カウント 。
MySQLSUBSTRING関数を理解するためのいくつかの例を見てみましょう。
文字列入力値を次のように取りましょう ソフトウェアテストヘルプ
文字列インデックスは次の表のようになります
| 1 | 二 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 十一 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| S | または | f | t | に | に | r | です | T | です | s | t | 私 | n | g | H | です | l | p |
MySQLSUBSTRINGの例
カウントなし
上記の文字列からヘルプテキストを抽出するとします–Hのインデックスは18です
以下のクエリを見てみましょう。
SELECT SUBSTRING('Software Testing Help', 18) as extracted_string; SELECT SUBSTRING('Software Testing Help' FROM 18) as extracted_string; // Output Help ここで重要なのは、 カウント、 ただし、まだ正しい文字列を取得しています。これは、カウントを省略すると、指定されたインデックスから始まる文字列全体が返されるためです。
この場合、開始インデックスについて言及すると、 例えば、 「T」の場合（インデックス10）
SELECT SUBSTRING('Software Testing Help', 10) as extracted_string; //Output Testing Help 上記のように、出力には「テストヘルプ」というテキスト全体が含まれています。
カウントあり
次に、返される文字数について説明する例を見てみましょう。
文字列「SoftwareTestingHelp」から「Testing」という単語を抽出したいとします。次に、カウントを– 7（「テスト」という単語の長さ）として言及できます。
以下のクエリを見てみましょう。
SELECT SUBSTRING('Software Testing Help', 10, 7) as extracted_string; SELECT SUBSTRING('Software Testing Help' FROM 10 FOR 7) as extracted_string; //Output Testing ここでは、必要な結果を得るためのクエリの一部として、開始インデックスと文字数の両方について説明しました。
負のインデックス値を使用
これまでのところ、これまで見てきた例では、 start_index 正の値として。
start_indexを負として指定することもできます。これは、MySQLエンジンが左からカウントする代わりに、文字列の末尾から開始インデックスをカウントしようとすることを意味します。
例を見てみましょう
文字列「ソフトウェアテストヘルプ」について、適切なインデックスを特定するために下に行を追加しましょう
| L | 1 | 二 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 十一 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| S | または | f | t | に | に | r | です | T | です | s | t | 私 | n | g | H | です | l | p | |||
| R | 21 | 20 | 19 | 18 | 17 | 16 | 15 | 14 | 13 | 12 | 十一 | 10 | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 二 | 1 |
したがって、ここの一番下の行は正しいインデックスを表しています。ここで、適切なインデックスを持つMySQLSUBSTRING関数を使用して「Testing」という単語を抽出するとします。
テストでの「T」の右側のインデックスは12です（左側のインデックスは10です）
SELECT SUBSTRING('Software Testing Help', -12, 7) as extracted_string; SELECT SUBSTRING('Software Testing Help' FROM -12 FOR 7) as extracted_string; //Output Testing 上記の例でわかるように、正しいインデックスを負の値として指定しても、同じ結果が得られます。
範囲外のインデックス
今、いくつかの否定的なシナリオを試してください
- 指定されたstart_indexが存在しない場合： つまり、指定されたインデックスが範囲外である場合、出力は空になります。
SELECT SUBSTRING('Software Testing Help', 30, 2) as extracted_string; //output Empty String - 指定されたカウントが負であるか、文字列の全長よりも大きい場合、出力は文字列の残りの部分になります（指定されたstart_indexを開始します）。
SELECT SUBSTRING('Software Testing Help', 5, 100) as extracted_string; //output ware Testing Help 上記のように、ここではカウントが100と記載されていますが、文字列の長さはわずか21文字です。
- カウントが負であると述べたシナリオをもう1つ試してみましょう。
SELECT SUBSTRING('Software Testing Help', 5, -20) as extracted_string; //output Empty String ここでも、エラーは発生せず、出力となる空の文字列のみが表示されます。
MySQL SUBSTRING_INDEX
これは、MySQLSUBSTRING関数の別のバリアントです。
ここでは、start_indexの実際の値を指定する代わりに、文字とインデックス、および出力を返す前に区切り文字を検索する必要がある回数を指定するだけです。
ここで注意すべき重要な点は SUBSTRING_INDEX
MySQL SUBSTRING INDEX関数：
SUBSTRING_INDEX({StringValue/Column},delimiter,n)- {StringValue / Column}： SELECTクエリの実際の文字列入力または列名。
- デリミタ： 残りの文字列を出力として抽出するために検索する必要があるのは、文字または部分文字列です。この値では大文字と小文字が区別されるため、実際の文字列に表示されるとおりに指定する必要があります。
- n： 区切り文字の数–つまり、文字列を返す前に区切り文字が検索される回数。
いくつかの例を参考にして、これを理解しましょう。
以下の次のセクションで説明するすべての例では、入力文字列と同じ文字列「ソフトウェアテストヘルプ」を使用します。
キャラクターを探す
文字「T」を検索すると、出力は「ソフトウェア」になります。 Softwareという単語の後の余分なスペースも含まれることに注意してください。
SELECT SUBSTRING_INDEX('Software Testing Help', 'T', 1) as extracted_string; //Output Software 複数回出現しているキャラクターを検索します。ここにあります–「e」
SELECT SUBSTRING_INDEX('Software Testing Help', 'e', 1) as extracted_string; //Output Softwar この例では： カウントを1に指定したため、「e」が最初に出現するまで、出力は文字列になります。
同じ文字「e」のカウントを2に変更します。
SELECT SUBSTRING_INDEX('Software Testing Help', 'e', 2) as extracted_string; //Output Software T これで、文字「e」が2番目に出現するまでの部分文字列が返されることがわかります。
別の例を見て、SUBSTRINGINDEXでは大文字と小文字が区別されることを理解してください。 「T」の代わりに「t」の文字を試してみましょう
アニメをオンラインで見るのに最適なウェブサイト
SELECT SUBSTRING_INDEX('Software Testing Help', 't', 1) as extracted_string; //Output Sof 文字列の検索
文字の代わりに文字列を検索するためにSUBSTRINGINDEX関数を使用できます。 「テスト」という単語を検索してみましょう。
SELECT SUBSTRING_INDEX('Software Testing Help', 'Testing', 1) as extracted_string; //Output Software 存在しない文字列を検索する
存在しない文字列でSUBSTRINGINDEXを使用する。
SELECT SUBSTRING_INDEX('Software Testing Help', 'abc', 1) as extracted_string; //Output Software Testing Help ここでの出力は少し予想外かもしれません。ただし、MySQL Engineは必要な一致を見つけることができないため、文字列全体を出力として返します。
文字または文字列を逆の順序で検索する
SUBSTRING関数と同様に、SUBSTRING INDEX関数でも、カウントを負の数として指定できます。これは、左から右にトラバースする代わりに、MySQLエンジンが右から左に一致するものを見つけようとすることを意味します。
いくつかの例の助けを借りてこれを理解してください。
SELECT SUBSTRING_INDEX('Software Testing Help', 'T', -1) as extracted_string; //Output esting Help 上記の例では、カウントが-1であるのに対し、区切り文字は「T」であることがわかります。したがって、出力は、文字「T」の最初の一致が取得されるまで、右から部分文字列になります。
MySQLSUBSTRING関数のアプリケーション
通常、1つまたは複数のテーブルからデータをクエリするときにMySQL文字列関数を使用します。列内の値を分割するため、または必要に応じて重要な値を表示するために使用できます。
例： ID、名前、住所で構成される従業員詳細テーブルがあるとします。
また、アドレス列の形式は– {HouseNo}、{StreetName}
以下は、テーブルを作成してダミーデータを挿入するためのスクリプトです。
CREATE TABLE employee_details (name VARCHAR(100), address VARCHAR(100), age INT); INSERT INTO employee_details values ('Akash Malhotra', '22,Defence Colony', 24), ('Steve Wilson','12/4,Wilson Street',28), ('Monica Singh','190,Smith Lane',32); MySQL SUBSTRING関数を使用して、 家屋番号 以下に示すように、アドレス列から抽出されます。
SELECT name, SUBSTRING_INDEX(address,',',1) as house_no, address, age from employee_details出力：
| 名前 | house_no | 住所 | 年齢 |
|---|---|---|---|
| アカシュ・マルホートラ | 22 | 22、ディフェンスコロニー | 24 |
| スティーブウィルソン | 12/4 | 12/4、ウィルソンストリート | 28 |
| モニカ・シン | 190 | 190、スミスレーン | 32 |
上記のクエリでは、SUBSTRING_INDEX関数を使用して住所列から家番号を取得し、区切り文字として「、」を使用していることがわかります。
これと同様に、文字列を抽出して、より大きな文字列入力または列値から意味のある情報をフェッチしたいアプリケーションが多数あります。
よくある質問
Q＃1）文字列内で最初に出現するSUBSTRINGの位置を返すMySQL関数はどれですか？
回答： MySQLには、指定された文字列または列の値からSUBSTRINGを返すまたは抽出するための2つの関数が用意されています。
- 部分文字列
例：
SELECT SUBSTRING('Test Input',1,4) as extracted_string; // Output Test - 部分文字列インデックス
例：
SELECT SUBSTRING_INDEX('Test Input','t',1) as extracted_string; //Output Tes Q＃2）MySQLのSUBSTRING_INDEXとは何ですか？
回答： SUBSTRING_INDEX関数は、SUBSTRING関数の変形です。通常のSUBSTRING関数では、入力文字列の数値としてインデックスを指定する必要がありますが、SUBSTRING_INDEX関数の場合は、検索する文字または小さい部分文字列のいずれかを指定できます。
推奨読書= >> MySQLCOUNTチュートリアル
結論
このチュートリアルでは、MySQLのSUBSTRING関数とSUBSTRING_INDEX関数について学習しました。どちらの関数も、指定された入力文字列に対して部分文字列を抽出しますが、動作は少し異なります。
SUBSTRINGはインデックスを実際の数値として想定しますが、SUBSTRING_INDEXは区切り文字を文字または文字列値として指定でき、MySQLエンジンは必要な一致する文字列または文字に対して抽出できます。
これらの関数はどちらも、適切な要件に基づいて文字列抽出ロジックを使用して、複数の情報を含む列をサブ列に抽出できる場合に主に使用されます。