文字列操作

2つの文字列を比較して一致するかチェックする

文字を左のX文字目から○文字切り取る(Mid関数)

文字列から検索文字の位置を取得する(IndexOf)

検索文字を後ろから検索する(LastIndexOf)

文字列をバイト数で計算する

文字列の追加したい箇所に文字を入れる

ひらがなをカタカナに置換する

カタカナをひらがなに置換する

半角から全角へ変換する

全角から半角へ変換する

単語の先頭文字を大文字に変換する

文字列中に指定した文字が含まれるかのチェック

文字列から指定した文字数を削除する

ファイルパス(フルパス)からファイル名を取得する

カンマ区切り文字列の指定カンマ箇所を取得する

指定した文字の出現回数をカウントする

 

 

数値操作

数値であるかのチェック(IsNumeric)

四捨五入(Round)

消費税を計算する(8%、10%対応)

ランダム値を生成する(Rand)

数値をカンマで区切る

Decode関数

引数の最大の整数値を返す(FLOOR関数)

 

 

日付・時間操作

日付を加算する(Date型)

日付を加算する(timestamp型)

日付であるかのチェック(IsDate)

日付の差を計算する

西暦から和暦へ変換する

曜日を取得する

月末日を取得する

年月日の文字列から日付を作成する

次の月曜日、火曜日、○曜日を求める(next_day関数)

 

 

配列

配列に特定の文字列が含まれるかをチェックする

2つの配列を結合する

配列の要素数を取得する

配列が全て空かチェックする

配列の指定箇所に値を入れる(文字列)

配列の指定箇所に値を入れる(数値)

 

配列の指定箇所に値を入れる関数

配列の指定箇所に値を入れる(数値)

 

引数

引数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}

説明

配列の指定箇所に値を入れる(文字列)

 

引数

引数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}

説明

全角から半角へ変換する自作関数です。

自作関数を使わずSQLで変換する方法は一番下に記載しています。

 

あわせて読みたい

反対の半角→全角への変換はこちらです

https://postgresweb.com/post-2295

 

引数

引数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',
	'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
	);
	
	--数値の変換
	c_full = translate(c_full,
	'1234567890',
	'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ュョワゴドボ/?_’¥'); 
--【結果】:"xyz890ュョワゴドボ/?_'\"

 

 

(関数を使わず)SQLで変換する方法

上の関数を使わずSQLで変換する例です。

少し長いのと、カナの濁音には対応していませんのでご注意ください。

-- 全角から半角へ変換するSQL文です
select translate('ここに変換したい文字を入れる',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ'
);

-- 実行例
select translate('Az#アワ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ'
);
-- "Az#アワ"

説明

半角から全角へ変換する自作関数です。

自作関数を使わずSQLで変換する方法一番下に記載しています。

 

あわせて読みたい

反対の全角→半角への変換はこちらです

https://postgresweb.com/post-2297

 

 

 

引数

引数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',
	'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
	);
	
	--数値
	c_half = translate(c_half,
	'1234567890',
	'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ュョアイウガギ/?_''\'); 
--【結果】:xyz890ュョアイウガギ/?_’¥

 

 

(関数を使わず)SQLで変換する方法

上の関数を使わずSQLで変換する例です。

少し長いのと、カナの濁音には対応していませんのでご注意ください。

-- 半角から全角へ変換するSQL文です
select translate('ここに変換したい文字を入れる',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ'
);

-- 実行例
select translate('Az#アワ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ'
);
-- "Az#アワ"

説明

年月日の文字列から日付を作成する

 

引数

引数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"

説明

引数の値以下で最大の整数値を返す関数です。

 

引数

引数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

配列が全て空かチェックする

配列の中身が全て空なのかチェックする

(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(空でない)

 

他にもこんな関数があります(配列操作の関数)

配列に特定の文字列が含まれるかをチェックする

2つの配列を結合する

 

配列の重複値を除外する

配列の中で重複値を除外する関数です。

(引数で渡された配列に対し、重複値を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}

指定した文字の出現回数をカウントする

文字列から指定した文字の出現回数をカウントする

 

 

引数

引数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