SQL関数(文字列関数・正規表現・日付関数)

Tag:

文字列関数、正規表現、日付関数について紹介します。

文字列関数
文字列関数 (参考サイト)
関数 説明
CONCAT(str1,str2,…) 引数で指定した文字を結合する
INSERT(str,pos,len,newstr) str内の、位置 pos から始まる長さ len の部分文字列を文字列 newstr に置き換えた文字列を返す。

          例) SELECT INSERT('あいうえお', 3, 2, 'か');
              -> あいかお
          
LENGTH(str) 文字列 str の長さ(バイト)を返す。
CHAR_LENGTH(str) 文字列 str の長さ(文字数)を返す。
REPLACE(str,from_str,to_str) 文字列 str に含まれる文字列 from_str をすべて文字列 to_str に置換した文字列を返す。
SUBSTRING(str,pos,len) len 引数のない形式の場合は、文字列 str 内の位置 pos 以降の部分文字列を返す。 len 引数のある形式の場合は、文字列 str の位置 pos 以降の、len に指定された長さの部分文字列を返す。
GROUP_CONCAT() 複数レコードの値を結合する。

          例1) 条件に一致した全レコードの指定カラム名の値をカンマ(,)区切りで結合
          SELECT GROUP_CONCAT(カラム名 SEPARATOR ',') 
          FROM テーブル名 
          WHERE 条件
          
          例2)さらにカラム名を降順で結合
          SELECT GROUP_CONCAT(カラム名 ORDER BY カラム名 DESC SEPARATOR ',') 
          FROM テーブル名 
          WHERE 条件
          

※大文字小文字、半角全角を区別しないで一致するか判定する方法

select * 
from 対象テーブル名
where convert(対象カラム名 using utf8) collate utf8_unicode_ci like '%検索文字列%';
正規表現
REGEXP演算子を使用して正規表現によるパターンマッチングが行えます。
『カラム名 REGEXP パターン』と指定すると、カラムの値がマッチすれば1(=TRUE)を返し、マッチしなければ0(=FALSE)を返します。

/* 例) カラム(name)の値がアルファベットのみのレコードを抽出 */
SELECT id, name
FROM sampleTable
WHERE name REGEXP '^[a-z]+$';
日付関数
日付関数 (参考サイト)
関数 説明
NOW() 現在の日付を ‘YYYY-MM-DD HH:MM:SS’ フォーマットの値で戻します。
CURRENT_DATE() 現在の日付を ‘YYYY-MM-DD’ フォーマットの値で戻します。
CURRENT_TIME() 現在の時刻を ‘HH:MM:SS’ フォーマットの値で戻します。
DATE_FORMAT
(date,format)
date 値を format に基づいてフォーマットします。

          例) SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
          -> 'Saturday October 1997'
          
          例) SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
          -> '22:23:00'
          
          例) SELECT DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j');
          -> '4th 97 Sat 04 10 Oct 277'
          
          例) SELECT DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w');
          -> '22 22 10 10:23:00 PM 22:23:00 00 6'
          
DATE_ADD
(date,
INTERVAL expr unit)
date は、開始日を指定する DATETIME または DATE 値です。
expr は開始日に追加、または開始日から引かれる区間値を指定する表現です。
expr はストリングで、負のインターバルの場合は ‘-’ で始まることがあります。
unit は、表現が解釈されるべきユニットを示すキーワードです。

          例) SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 SECOND);
          -> '1998-01-01 00:00:00'
          
          例) SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 DAY);
          -> '1998-01-01 23:59:59'
          
          例) SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
          -> '1998-01-01 00:01:00'

          例) SELECT DATE_ADD('1998-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
          -> '1997-12-30 14:00:00'
          
DATEDIFF
(expr1,expr2)
DATEDIFF() は、ひとつの日付から他の日付への日数の値として表現された expr1 – expr2 を戻します。expr1 および expr2 は日付または日付と時刻の表現です。値の日付部分のみが計算に使用されます。

          例) SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
          -> -31
        

経過日数は下記のように指定しても取得できます。

to_days(`end_day`) - to_days(`start_day`)

上記の関数を利用した例を紹介します。

`working_day` という日付データを持つカラムの判定
■ 例1. 今週であるか判定
(YEARWEEK(DATE_FORMAT(`working_day`, '%Y/%m/%d'), 1)
 = YEARWEEK(NOW(), 1))

■ 例2. 来週であるか判定
(YEARWEEK(DATE_FORMAT(`working_day`, '%Y/%m/%d'), 1)
 = YEARWEEK(DATE_ADD(NOW(), INTERVAL 1 WEEK), 1))

スポンサーリンク