絶対値を取得する方法

数値の絶対値を取得するにはabsを使用します。

 

 

絶対値を取得する構文

--絶対値を取得する
abs( 数値 ) 

 

 

絶対値を取得する(abs)構文例

select * from abs(5);      -- 5
select * from abs(-5);     -- 5

--小数でも特に動作は変わりません
select * from abs(3.4);    -- 3.4
select * from abs(-3.4);   -- 3.4
select * from abs(3.45);   -- 3.45
select * from abs(-3.45);  -- 3.45

 

 

構文まとめを書いています。ぜひ一度見てみて下さい。

「基本構文・こんな時どう書くんだっけ?」をまとめました

文字を置換する、複数置換する方法

文字を置換するにはREPLACEと、TRANSLATEの二つの方法があります。

REPLACEは単語単位で置換するのに対し、TRANSLATEは文字列の先頭から1文字ずつ置換するという違いがあります。

 

 

文字を置換する(replace関数)の構文

文字を置換するにはreplace関数を使用します。

-- 文字を置換する構文
replace( 文字列,検索文字列,置換文字列 ) 

 

 

文字を一つ置換する(replace関数)の使用例

replace関数の使用例です。

-- 'everywhere'から'e'を'E'に置換する
select replace('everywhere','e','E');  --"EvErywhErE"

-- 'ABCDE'から'ABC'を'abc'に置換する
select replace('ABCDE','ABC','abc');   --"abcDE"

-- 検索文字列がない場合、エラーにはならずそのままの文字が返る
select replace('ABCDE','XYZ','abc');   --"ABCDE"

 

 

文字を複数置換する(translate関数)の構文

文字を複数置換するには、translate関数を使用します。

-- 文字を置換する構文
translate( 文字列,検索文字列,置換文字列 ) 

-- 検索文字列は、1文字ずつ順番に置換文字列に置換する
-- 次の文は、aをAに、dをDに、gをGに置換することを示す
translate( 文字列,'adg','ADG' ) ;

 

 

文字を複数置換する(translate関数)の使用例

translate関数の使用例です。

-- 'everywhere'から'e'を'E'に、'r'を'R'に置換する
select translate('everywhere','er','ER');  --"EvERywhERE"

-- 'ABCDEFGHI'から'a'を'A'に、'd'を'D'に、'g'を'G'に置換する
select translate('abcdefghi','adg','ADG');   --"AbcDefGhi"

-- 検索文字列がない場合、エラーにはならずそのままの文字が返る
select translate('ABCDE','XYZ','abc');   --"ABCDE"

 

 

関連記事(文字列操作)

文字列の結合、nullの結合

改行コードで結合、置換する

文字数のカウント(length)、バイト数を取得する

文字の切り取り(Left、Right、Substring)

検索文字位置の取得(strpos)

全角半角スペースを除去する(trim)

upper(小文字を大文字に変換する)

大文字小文字を変換する、区別せず比較する

前0埋め、後ろ0埋め(lpad、rpad)

Nullの時に別の値に置き換える

大文字小文字を変換する、区別せず比較する方法

大文字小文字を変換する、区別せず比較する方法を紹介します。

 

 

小文字から大文字に変換する

小文字から大文字に変換するにはupperを使用します。

※ ロケールが「C」の時は、全角小文字がupperされないため注意して下さい。

-- 小文字 → 大文字へ変換する
upper( 変換する文字列 )

-- 変換例   ※下の例はそのまま実行できます
select upper('abc');     --"ABC"
select upper('aBc');     --"ABC"
select upper('abc');  --"ABC"

 

 

大文字から小文字に変換する

大文字から小文字に変換するにはlowerを使用します。

※ ロケールが「C」の時、全角小文字がlowerされないため注意して下さい。

-- 大文字 → 小文字変換へ変換する
lower( 変換する文字列 )

-- 変換例   ※下の例はそのまま実行できます
select lower('ABC');     --"abc"
select lower('aBc');     --"abc"
select lower('ABC');  --"abc"

 

 

大文字小文字を区別せず比較する

大文字小文字を区別したくない場合には、どちらの文字に合わせて比較します。

※ 次の例では大文字に合わせて比較しています。

-- 大文字に変換したもの同士で比較する
select upper('abc') = upper('ABC');             --true

-- 大文字に変換したもの同士+trimして比較する
select upper(trim('abc')) = upper(trim('ABC')); --true

 

 

かなり緩めな文字列比較

かなり緩めに文字列を比較したい場合の例を紹介します。

例えば、大文字小文字、全半角、前後のスペースが異なっても同じとみなしたい場合です。

例:APPLEとapple、BLUEとblue など

-- それぞれの文字をtrim後、小文字を大文字に変換しさらに半角から全角に変換して比較する
select convert_half_to_full(upper(trim('abc '))) 
       = convert_half_to_full(upper(trim('ABC '))); --true

 

あわせて読みたい

上の例は、この自作関数「convert_half_to_full」を使用しています

https://postgresweb.com/post-2295

 

説明

文字列の前後にあるスペース、間にあるスペースを取り除く方法を紹介します。

 

 

半角スペースを除去する

半角スペースを除去する例です(そのまま実行できます)。

--スペースを除外する文字列(この文字を使って例を示します)
select ' a b c ';  --" a b c "

--半角スペースをすべて除去する(replace)
select replace(' a b c ',' ','');  --"abc"

--文字の前後の半角スペースを除去する(trim)
select trim(' a b c ');            --"a b c"

--文字の左部分の半角スペースを除去する(ltrim)
select ltrim(' a b c ');           --"a b c "

--文字の右部分の半角スペースを除去する(rtrim)
select rtrim(' a b c ');           --" a b c"

 

あわせて読みたい

上の例で使用しているreplaceは、こちらで解説しています

https://postgresweb.com/post-4883

 

 

 

全角スペースを除去する

全角スペースを除去する例です(そのまま実行できます)。

--文字の前後の全角スペースを除去する
select trim(replace(' a b c ',' ',' '));  --"a b c"

--全角スペースをすべて除去
select replace(' a b c ',' ','');         --"abc" 

 

 

全半角スペースを除去する

全角と半角スペース共に除去する例です(そのまま実行できます)。

--文字の前後、文字の間にある全角、半角スペースを取り除く
select replace(replace(' a b c ',' ',' '),' ','');  --"abc"

 

 

関連記事(文字列操作)

文字列の結合、nullの結合

改行コードで結合、置換する

文字数のカウント(length)、バイト数を取得する

文字の切り取り(Left、Right、Substring)

検索文字位置の取得(strpos)

upper(小文字を大文字に変換する)

大文字小文字を変換する、区別せず比較する

文字を置換する、複数置換する(replace、translate)

前0埋め、後ろ0埋め(lpad、rpad)

Nullの時に別の値に置き換える

 

改行コードで結合する方法

改行コードは全部で3種類あるので、注意が必要です。

よくわからない場合は、次の例を実行してみてうまくいくものを使ってみましょう。

例は、文字1と文字列2を結合している文です。(そのまま実行できます)

--改行コード(Windowsの場合):chr(13) + chr(10) を使う
select '文字1' || chr(13) || chr(10) || '文字2';

--改行コード2(Mac OS(9以前)の場合):chr(13)を使う
select '文字1' || chr(13) || '文字2'; 

--改行コード3 UNIX系の場合:chr(10)を使う
select '文字1' || chr(10) || '文字2';

chr(13) : CR キャリッジ・リターン

chr(10) : LF ラインフィールド

chr(13) + chr(10) : CRLF を示しています。

 

 

改行コードを置換する

次は、改行コードを置換する例です。

--改行コードを置換して、1行にする文
--『'\r|\n|\r\n'』と『'g'』は変更せずそのまま使ってください。
select REGEXP_REPLACE('文字列','\r|\n|\r\n', '改行をこの文字に置換','g');


--3行になっている文の改行を置換して、1行にする文
select REGEXP_REPLACE('あいうえお
かきくけこ
さしすせそ','\r|\n|\r\n', '','g');       -- あいうえおかきくけこさしすせそ


--改行コードを別の文字(ここでは「改行」)に置換する例 
select REGEXP_REPLACE('あいう
かきく
さしす','\r|\n|\r\n', '改行','g');    -- あいう改行かきく改行さしす

'\r|\n|\r\n' … 「\r」、「\n」、「\r\n」を空文字("")に置換

'g' … すべての改行を置換 を表しています。

 

 

PostgreSQLの構文まとめを書いています

「基本構文・こんな時どう書くんだっけ?」のまとめ

文字列を結合する方法

文字列の結合には「||」を使用します。

|| … シフトキーを押しながら¥のキーで入力します。

select 'abc' || 'DEF';           -- "abcDEF"
select 'abc' || 'DEF' || '123';  -- "abcDEF123"

 

 

nullの結合(concat)

結合する文字列にnullが含まれる場合、結合した結果はnullとなってしまいます。

それを防ぐためには、concatを使用します。

--concatを使用するとnull文字は無視されて連結されます。
concat( 結合する文字列1 , 結合する文字列2 , ・・・)

--nullと結合するとnullになる例
select 'abc' || 'DEF' || null;        -- null

--concatの使用例(そのまま実行できます)
select concat('abc' , 'DEF' , null);  -- "abcDEF"

 

 

nullの結合(coalesce)

concatではなく、coalesceを使用する方法もあります。

coalesceは一つ目の引数がnullの時、二つ目の引数を返す関数です。

--coalesce
coalesce( 文字列 , 左の文字列がnullの時ここで指定した文字を返す)

--coalesce例1 nullの時'abc'で返す
select coalesce(null , 'abc') ; -- "abc"

--coalesce例2 結合する文字列それぞれにcoalesceを付けて結合する
select coalesce(null,'abc') || coalesce(null,'DEF'); -- "abcDEF"

 

あわせて読みたい

https://postgresweb.com/post-1593

 

文字数のカウント、バイト数を取得する方法

文字数の取得にはlengthを、バイト数の取得にはoctet_lengthを使用します。

 

 

文字数を取得する(length)

半角全角関わらず1文字を1としてカウントします。

--文字数を取得する
select length(文字列);

--lengthの例
select length('abcdefg');    -- 7
select length('アイウエオ');       -- 5
select length('あいうえお');  -- 5
select length('アイウエオ');  -- 5

 

 

文字列のバイト数を取得(octet_length)

半角=1、全角=3バイト、半角カタカナは1文字3バイトで計算されます。

--文字のバイト数を取得する
select octet_length(文字列);

--octet_lengthの例
select octet_length('abcdefg');   -- 7
select octet_length('アイウエオ');     -- 15
select octet_length('あいうえお'); -- 15
select octet_length('アイウエオ'); -- 15

 

 

その他の文字列操作

文字列の結合、nullの結合

改行コードで結合、置換する

文字の切り取り(Left、Right、Substring)

検索文字位置の取得(strpos)

全角半角スペースを除去する(trim)

upper(小文字を大文字に変換する)

大文字小文字を変換する、区別せず比較する

文字を置換する、複数置換する(replace、translate)

前0埋め、後ろ0埋め(lpad、rpad)

Nullの時に別の値に置き換える

配列の先頭・末尾に追加する

配列の先頭・末尾に追加する例です。そのまま実行できる例を用意しました。

--単純な配列
select array['a','b','c']; --{a,b,c}

--配列の先頭に加える
--array_prepend(加えるもの , 配列)
select array_prepend('0',array['a','b','c']); --{0,a,b,c}

--配列の末尾に加える
--array_append(配列 , 加えるもの)
select array_append(array['a','b','c'],'d'); --{a,b,c,d}

--配列に配列を加える
select array_cat(array['a','b'], array['c','d']); --{a,b,c,d}

 

構文まとめを書いています。ぜひ一度見てみて下さい

「基本構文・こんな時どう書くんだっけ?」をまとめました

where文で配列を使う

配列の中のいずれかの値で検索をするには、any(配列)を使います。

※ 配列の中の値どれかに合致すれば、その行が取得できます。

-- 構文
select * from テーブル名 where 列名 = any(配列);

-- 例
select * from m_school where seq = any(array[1,2,3]);                -- 数値
select * from m_school where school_code = any(array['A','B','C']);  -- 文字
select * from m_school where start_date 
= any(array[cast('2021/5/1' as date),cast('2021/5/3' as date)]);     -- 日付

説明

配列をループさせる簡単な実行例があまりないと感じ、サンプル関数を用意しました。

pgAdminのクエリツールからすぐに実行できるようにしましたので、実行し結果を確かめてみて下さい。

 

 

コード

CREATE OR REPLACE FUNCTION testfnc()
RETURNS boolean
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$
declare
	loopcount integer;
	arr character varying[];
begin

	--arrは配列
	arr = array['A','B','C','D'];

	--配列の中身をループで表示する
	for loopcount in 1..array_length(arr,1) loop
		raise info '%' , arr[loopcount]; 
	end loop;

	return true;
	
end;
$BODY$;
ALTER FUNCTION testfnc() OWNER TO postgres;

--関数を実行する文↓
select * from testfnc();

 

 

実行結果について

実行した場合、「true」が返ってくるだけだと思いますが、「メッセージ」タブを表示すると配列の中身が表示されていると思います。

あとは適宜、中身を変えて実行してみて下さい。

 

 

【補足】For文について

For文の構文は次のように書きます。

--For文の構文
FOR 変数名 IN 初期値..終了値 LOOP
処理
END LOOP;

 

配列の要素数はarray_length()で取得できます。

2つ目の引数は配列の次元です。1次元の配列なら1を指定します。

--配列の要素数を取得する
array_length( 配列 , 配列の次元 )

 

これらを使ってサンプル関数では次のようにしています。

--ループ用の変数を「loopcount」とし、1から配列の要素数分ループさせる
for loopcount in 1..array_length(arr,1) loop

--raise infoで変数の中身を表示する
raise info '%' , arr[loopcount];
 
end loop;

 

 

数値型の配列バージョン

需要があるかわかりませんが文字型ではなく、数値型の配列も用意してみました。

CREATE OR REPLACE FUNCTION testfnc()
RETURNS boolean
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$
declare
	loopcount integer;
	arr integer[];
begin

	--arrは配列
	arr = array[1,10,100,1000];

	--配列の中身をループで表示する
	for loopcount in 1..array_length(arr,1) loop
		raise info '%' , arr[loopcount]; 
	end loop;

	return true;
	
end;
$BODY$;
ALTER FUNCTION testfnc() OWNER TO postgres;

--関数を実行する
select * from testfnc();


--関数を実行する
select * from testfnc();

以上、配列をループさせるサンプルでした。

 

 

関連記事(配列)

・配列の先頭・末尾に追加する

・配列の値で抽出する(Where、any)

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

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

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

・配列の重複値を除外する

・配列の要素数を取得する

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

・2つの配列を結合する