文字列操作
数値操作
日付・時間操作
・次の月曜日、火曜日、○曜日を求める(next_day関数)
配列
【PostgreSQL】配列の指定箇所に値を入れる（数値）
配列の指定箇所に値を入れる関数
配列の指定箇所に値を入れる（数値）
引数
引数1(character varying[])：挿入する配列
引数2(numeric)：挿入する場所（1からカウント）
引数3(character varying)：挿入する数値
返り値
値を挿入した後の配列
コード
CREATE OR REPLACE FUNCTION insert_into_array_num(
numeric[],
numeric,
numeric)
RETURNS numeric[] AS
$BODY$
declare
a_array alias for $1; --引数1:挿入する配列
insert_pos alias for $2; --引数2:挿入する場所（1からカウント）
c_insert_char alias for $3; --引数3:挿入する数値
return_array numeric[];
begin
for temp_count in 1..array_length(a_array,1)
loop
--返り値用の配列に値をセット
if temp_count < insert_pos then
--ループカウント < 引数2(insert_pos)の時、元の配列の値をセット return_array[temp_count] = a_array[temp_count]; end if; if temp_count = insert_pos then --ループカウント = 引数2(insert_pos)の時、引数3(挿入する文字列)をセット return_array[temp_count] = c_insert_char; end if; if temp_count > insert_pos then
--ループカウント > 引数2(insert_pos)の時、元の配列の値（配列の添え字は-1）をセット
return_array[temp_count] = a_array[temp_count -1] ;
end if;
if temp_count = array_length(a_array,1) then
--最後に元の配列の最後の値をセット
return_array[temp_count +1 ] = a_array[temp_count];
end if;
end loop;
return return_array;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION insert_into_array_num(numeric[], numeric, numeric)
OWNER TO postgres;
実行例
--配列[123,234,345,456,567]の3番目に999を入れる
select * from insert_into_array_num(array[123,234,345,456,567],3,999)
--{123,234,999,345,456,567}
【PostgreSQL】配列の指定箇所に値を入れる（文字列）
説明
配列の指定箇所に値を入れる（文字列）
引数
引数1(character varying[])：挿入する配列
引数2(numeric)：挿入する場所（1からカウント）
引数3(character varying)：挿入する文字列
返り値
値を挿入した後の配列
コード
CREATE OR REPLACE FUNCTION insert_into_array_char(
character varying[],
numeric,
character varying)
RETURNS character varying[] AS
$BODY$
declare
a_array alias for $1; --引数1:挿入する配列
insert_pos alias for $2; --引数2:挿入する場所（1からカウント）
c_insert_char alias for $3; --引数3:挿入する文字列
return_array character varying[];
begin
for temp_count in 1..array_length(a_array,1)
loop
--返り値用の配列に値をセット
if temp_count < insert_pos then
--ループカウント < 引数2(insert_pos)の時、元の配列の値をセット return_array[temp_count] = a_array[temp_count]; end if; if temp_count = insert_pos then --ループカウント = 引数2(insert_pos)の時、引数3(挿入する文字列)をセット return_array[temp_count] = c_insert_char; end if; if temp_count > insert_pos then
--ループカウント > 引数2(insert_pos)の時、元の配列の値（配列の添え字は-1）をセット
return_array[temp_count] = a_array[temp_count -1] ;
end if;
if temp_count = array_length(a_array,1) then
--最後に元の配列の最後の値をセット
return_array[temp_count +1 ] = a_array[temp_count];
end if;
end loop;
return return_array;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION insert_into_array_char(character varying[], numeric, character varying)
OWNER TO postgres;
※PostgreSQL12で動作確認済み
実行例
select * from insert_into_array_char(array['A','B','C','D','E','F'],3,'Z')
--{A,B,Z,C,D,E,F}
【PostgreSQL】全角から半角へ変換する
説明
全角から半角へ変換する自作関数です。
自作関数を使わずSQLで変換する方法は一番下に記載しています。
引数
引数1(character varying)：変換する文字列
返り値
変換後の文字列
コード
CREATE OR REPLACE FUNCTION convert_full_to_half(c_full character varying)
RETURNS character varying AS
$BODY$
declare
begin
--アルファベットの変換
c_full = translate(c_full,
'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
);
--数値の変換
c_full = translate(c_full,
'１２３４５６７８９０',
'1234567890'
);
--カタカナ（濁音）
c_full := replace(replace(replace(replace(replace(replace(c_full,'ヴ','ｳﾞ' ),'ガ','ｶﾞ' ),'ギ','ｷﾞ' ),'グ','ｸﾞ' ),'ゲ','ｹﾞ' ),'ゴ','ｺﾞ' );
c_full := replace(replace(replace(replace(replace(c_full,'ザ','ｻﾞ' ),'ジ','ｼﾞ' ),'ズ','ｽﾞ' ),'ゼ','ｾﾞ' ),'ゾ','ｿﾞ' );
c_full := replace(replace(replace(replace(replace(c_full,'ダ','ﾀﾞ' ),'ヂ','ﾁﾞ' ),'ヅ','ﾂﾞ' ),'デ','ﾃﾞ' ),'ド','ﾄﾞ' );
c_full := replace(replace(replace(replace(replace(c_full,'バ','ﾊﾞ' ),'ビ','ﾋﾞ' ),'ブ','ﾌﾞ' ),'ベ','ﾍﾞ' ),'ボ','ﾎﾞ' );
c_full := replace(replace(replace(replace(replace(c_full,'パ','ﾊﾟ' ),'ピ','ﾋﾟ' ),'プ','ﾌﾟ' ),'ペ','ﾍﾟ' ),'ポ','ﾎﾟ' );
--カタカナ
c_full = translate(c_full,
'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ',
'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｶｹｯｬｭｮﾜ'
);
--記号
c_full = translate(c_full,
' ！”＃＄％＆（）ー－＝＾～｜＠‘「［｛；＋：＊」］｝、，＜。．＞・／？＿',
' !"#$%&()ｰ-=^~|@`｢[{;+:*｣]}､,<｡.>･/?_'
);
c_full := replace(c_full,'’','''' );
c_full := replace(c_full,'￥',chr(92) );
return c_full;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
※ PostgreSQL9.5 , 9.6 , 10 , 11 , 12 , 13で動作確認済み 自作関数の実行方法はこちら
実行例
select * From convert_full_to_half('ｘｙｚ８９０ュョワゴドボ／？＿’￥');
--【結果】:"xyz890ｭｮﾜｺﾞﾄﾞﾎﾞ/?_'\"
（関数を使わず）SQLで変換する方法
上の関数を使わずSQLで変換する例です。
少し長いのと、カナの濁音には対応していませんのでご注意ください。
-- 全角から半角へ変換するSQL文です
select translate('ここに変換したい文字を入れる',
'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ１２３４５６７８９０ ！”＃＄％＆（）ー－＝＾～｜＠‘「［｛；＋：＊」］｝、，＜。．＞・／？＿アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ｰ-=^~|@`｢[{;+:*｣]}､,<｡.>･/?_ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｶｹｯｬｭｮﾜ'
);
-- 実行例
select translate('Ａｚ＃アワ',
'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ１２３４５６７８９０ ！”＃＄％＆（）ー－＝＾～｜＠‘「［｛；＋：＊」］｝、，＜。．＞・／？＿アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ｰ-=^~|@`｢[{;+:*｣]}､,<｡.>･/?_ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｶｹｯｬｭｮﾜ'
);
-- "Az#ｱﾜ"
【PostgreSQL】半角から全角へ変換する
説明
半角から全角へ変換する自作関数です。
自作関数を使わずSQLで変換する方法は一番下に記載しています。
引数
引数1(character varying)：変換する文字列
返り値
変換後の文字列
コード
この関数はコピー＆貼り付けで実行できます。
CREATE OR REPLACE FUNCTION convert_half_to_full(c_half character varying)
RETURNS character varying AS
$BODY$
declare
begin
--アルファベットの変換
c_half = translate(c_half,
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ'
);
--数値
c_half = translate(c_half,
'1234567890',
'１２３４５６７８９０'
);
--カタカナ（濁音）
c_half := replace(replace(replace(replace(replace(replace(c_half,'ｳﾞ','ヴ' ),'ｶﾞ','ガ' ),'ｷﾞ','ギ' ),'ｸﾞ','グ' ),'ｹﾞ','ゲ' ),'ｺﾞ','ゴ' );
c_half := replace(replace(replace(replace(replace(c_half,'ｻﾞ','ザ' ),'ｼﾞ','ジ' ),'ｽﾞ','ズ' ),'ｾﾞ','ゼ' ),'ｿﾞ','ゾ' );
c_half := replace(replace(replace(replace(replace(c_half,'ﾀﾞ','ダ' ),'ﾁﾞ','ヂ' ),'ﾂﾞ','ヅ' ),'ﾃﾞ','デ' ),'ﾄﾞ','ド' );
c_half := replace(replace(replace(replace(replace(c_half,'ﾊﾞ','バ' ),'ﾋﾞ','ビ' ),'ﾌﾞ','ブ' ),'ﾍﾞ','ベ' ),'ﾎﾞ','ボ' );
c_half := replace(replace(replace(replace(replace(c_half,'ﾊﾟ','パ' ),'ﾋﾟ','ピ' ),'ﾌﾟ','プ' ),'ﾍﾟ','ペ' ),'ﾎﾟ','ポ' );
--カタカナ
c_half = translate(c_half,
'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｶｹｯｬｭｮﾜ',
'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ'
);
--記号
c_half = translate(c_half,
' !"#$%&()ｰ-=^~|@`｢[{;+:*｣]}､,<｡.>･/?_',
' ！”＃＄％＆（）ー－＝＾～｜＠‘「［｛；＋：＊」］｝、，＜。．＞・／？＿'
);
c_half := replace(c_half,'''','’' );
c_half := replace(c_half,chr(92),'￥' );
return c_half;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
※ PostgreSQL9.5 , 9.6 , 10 , 11 , 12 , 13で動作確認済み 自作関数の実行方法はこちら
実行例
select * From convert_half_to_full('xyz890ｭｮｱｲｳｶﾞｷﾞ/?_''\');
--【結果】：ｘｙｚ８９０ュョアイウガギ／？＿’￥
（関数を使わず）SQLで変換する方法
上の関数を使わずSQLで変換する例です。
少し長いのと、カナの濁音には対応していませんのでご注意ください。
-- 半角から全角へ変換するSQL文です
select translate('ここに変換したい文字を入れる',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ｰ-=^~|@`｢[{;+:*｣]}､,<｡.>･/?_ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｶｹｯｬｭｮﾜ',
'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ１２３４５６７８９０ ！”＃＄％＆（）ー－＝＾～｜＠‘「［｛；＋：＊」］｝、，＜。．＞・／？＿アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ'
);
-- 実行例
select translate('Az#ｱﾜ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ｰ-=^~|@`｢[{;+:*｣]}､,<｡.>･/?_ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｶｹｯｬｭｮﾜ',
'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ１２３４５６７８９０ ！”＃＄％＆（）ー－＝＾～｜＠‘「［｛；＋：＊」］｝、，＜。．＞・／？＿アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ'
);
-- "Ａｚ＃アワ"
【PostgreSQL】年月日の文字列から日付を作成する
説明
年月日の文字列から日付を作成する
引数
引数1(character varying)：文字列（年）
引数2(character varying)：文字列（月）
引数3(character varying)：文字列（日）
返り値
文字から作った日付（数値に変換できなかった時はNullが返ります）
コード
CREATE OR REPLACE FUNCTION get_char_to_date(
character varying,
character varying,
character varying)
RETURNS date AS
$BODY$
declare
c_year alias for $1; --引数1:文字列（年）
c_month alias for $2; --引数2:文字列（月）
c_day alias for $3; --引数3:文字列（日）
c_temp character varying;
d_temp character varying;
c_execute character varying;
begin
--yyyy/mm/ddの文字列を作成
c_temp = c_year || '/' || c_month || '/' || c_day;
--日付変換できるかチェック
c_execute := 'SELECT date(''' || c_temp || ''')';
EXECUTE c_execute INTO d_temp;
--日付だったらそのまま返す
return d_temp;
EXCEPTION
WHEN OTHERS THEN
--日付でなかったらnullで返す
return null;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION get_char_to_date(character varying, character varying, character varying)
OWNER TO postgres;
※PostgreSQL12で動作確認済み
実行例
select * from get_char_to_date('2019','4','1');
--"2019-04-01"
【PostgreSQL】引数の最大の整数値を返す（FLOOR関数）
説明
引数の値以下で最大の整数値を返す関数です。
引数
引数1(numeric)：丸める対象の数値
返り値
整数値
コード
CREATE OR REPLACE FUNCTION floor(numeric)
RETURNS integer AS
$BODY$
declare
a_target alias for $1; --引数1:丸める対象の数値
begin
return trunc(a_target,0);
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION floor(numeric)
OWNER TO postgres;
※PostgreSQL12で動作確認済み
実行例
select * from floor(200 * 0.977);
--195
【PostgreSQL】配列が全て空かチェックする
配列が全て空かチェックする
配列の中身が全て空なのかチェックする
（Nullまたは""の時、空であると判定します。）
引数
引数1(character varying[])：チェックする配列
返り値
True：全て空である、False：空でない
コード
CREATE OR REPLACE FUNCTION check_empty_array(character varying[])
RETURNS boolean AS
$BODY$
declare
a_array alias for $1; -- 引数1:チェックする配列
b_flg boolean; -- True:全て空である、False:空でない
begin
b_flg = true;
for loop_count in 1..array_length(a_array,1)
loop
--Nullまたは""の時、空であると判定
if (a_array[loop_count] = '') or (a_array[loop_count] is null) then
else
b_flg = false;
end if;
end loop;
return b_flg;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION check_empty_array(character varying[])
OWNER TO postgres;
※ PostgreSQL12で動作確認済み
実行例
select * From check_empty_array(array['',null,null,'']);
-- true（空である）
select * From check_empty_array(array['a','b','c']);
-- false（空でない）
他にもこんな関数があります（配列操作の関数）
【PostgreSQL】配列の重複値を除外する
配列の重複値を除外する
配列の中で重複値を除外する関数です。
（引数で渡された配列に対し、重複値を1つにまとめ、すべての値が一つずつの状態にします）
引数
引数1(integer[])：数値の配列
返り値
重複した値を除外した配列
コード（そのまま実行できます）
CREATE OR REPLACE FUNCTION exclude_duplicate(integer[])
RETURNS integer[] AS
$BODY$
declare
a_array alias for $1; -- 引数1 : 除外対象の配列（数値）
begin
return array(
SELECT DISTINCT co11 FROM
(
SELECT unnest(a_array) AS co11 order by 1
) AS temp
);
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION exclude_duplicate(integer[]) OWNER TO postgres;
実行例
select * from exclude_duplicate(array[12,23,34,12,23,34,45,56]);
--【結果】:{12,23,34,45,56}
【PostgreSQL】指定した文字の出現回数をカウントする
指定した文字の出現回数をカウントする
文字列から指定した文字の出現回数をカウントする
引数
引数1(character varying)：対象の文字列
引数2(character varying)：探す文字列
返り値
文字の出現回数（数値）
コード
CREATE OR REPLACE FUNCTION count_appearances(
character varying,
character varying)
RETURNS integer AS
$BODY$
declare
c_target_char alias for $1; --引数1:対象の文字列
c_seach_char alias for $2; --引数2:探す文字列
begin
return (length(c_target_char) - length(replace(c_target_char, c_seach_char, ''))) / length(c_seach_char);
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
実行例
--文字列'AAbbccddefghiA'から'A'の個数をカウントする
select * From count_appearances('AAbbccddefghiA','A'); --結果：3
--文字列'abcdefabcdef'から'abc'の個数をカウントする
select * From count_appearances('abcdefabcdef','abc'); --結果：2