標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある Tweet
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の12日目です。
私も癖多めですw
とうことで、アドベントカレンダーも約半分の折り返し地点です。ふー。
今日は、INSTR()
Oracleでは、SUBSTR()同様のバリエーションと挙動が見られます。ある意味分かり易ですね。
と言うことは、方言になりやすいはず。とも言えるわけです。はい。
では、見ていきましょう。
Oracle
positionは、SUBSTR()同様に負の値が使えます。0は、0しか返しません。
occurrenceは、正の値のみを取ります。
{ INSTR| INSTRB| INSTRC| INSTR2| INSTR4}(string , substring [, position [, occurrence ] ])
https://docs.oracle.com/cd/F19136_01/sqlrf/INSTR.html#GUID-47E3A7C4-ED72-458D-A1FA-25A9AD3BE113
ORACLE> SELECT INSTR('1234a56789a', 'a') FROM dual;
INSTR('1234A56789A','A')
------------------------
5
ORACLE> SELECT INSTR('1234a56789a', 'a', 1) FROM dual;
INSTR('1234A56789A','A',1)
--------------------------
5
ORACLE> SELECT INSTR('1234a56789a', 'a', -1) FROM dual;
INSTR('1234A56789A','A',-1)
---------------------------
11
ORACLE> SELECT INSTR('1234a56789a', 'a', 1, 1) FROM dual;
INSTR('1234A56789A','A',1,1)
----------------------------
5
ORACLE> SELECT INSTR('1234a56789a', 'a', 1, 2) FROM dual;
INSTR('1234A56789A','A',1,2)
----------------------------
11
ORACLE> SELECT INSTR('1234a56789a', 'a', -1, 2) FROM dual;
INSTR('1234A56789A','A',-1,2)
-----------------------------
5
ORACLE> SELECT INSTR('1234a56789a', 'a', -1, 1) FROM dual;
INSTR('1234A56789A','A',-1,1)
-----------------------------
11
ORACLE> SELECT INSTR('1234a56789a', 'a', 0) FROM dual;
INSTR('1234A56789A','A',0)
--------------------------
0
ORACLE> set null [NULL]
ORACLE> SELECT INSTR('1234a56789a', 'a', null) FROM dual;
INSTR('1234A56789A','A',NULL)
-----------------------------
[NULL]
ORACLE> SELECT INSTR('1234a56789a', 'a', '') FROM dual;
INSTR('1234A56789A','A','')
---------------------------
[NULL]
ORACLE> SELECT INSTR('1234a56789a', '') FROM dual;
INSTR('1234A56789A','')
-----------------------
[NULL]
ORACLE> SELECT INSTR('1234a56789a', NULL) FROM dual;
INSTR('1234A56789A',NULL)
-------------------------
[NULL]
ORACLE> SELECT INSTR('1234a56789a', 'a', 1, 0) FROM dual;
SELECT INSTR('1234a56789a', 'a', 1, 0) FROM dual
*
ERROR at line 1:
ORA-01428: argument '0' is out of range
ORACLE> SELECT INSTR('1234a56789a', 'a', 1, -1) FROM dual;
SELECT INSTR('1234a56789a', 'a', 1, -1) FROM dual
*
ERROR at line 1:
ORA-01428: argument '-1' is out of range
MySQL
SUBSTR()はOracleに類似した挙動を持つ部分が多かったMySQLもINSTR()についてはそうでもないですね。positionやoccurrenceなどの引数がありません。
ただ、LOCATE()と言う類似した関数があります。LOCATE()と言う関数ではposition引数がありますが、０以上の整数でのみOracleと同じ挙動で負の値は、常に０ゼロを返すようです。
positionやoccurrence を利用している場合の移行は要注意と言うところですね。
INSTR(str,substr)
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_instr
mysql> SELECT INSTR('1234a56789a', 'a');
+---------------------------+
| instr('1234a56789a', 'a') |
+---------------------------+
| 5 |
+---------------------------+
1 row in set (0.04 sec)
mysql> SELECT INSTR('1234a56789a', '');
+--------------------------+
| instr('1234a56789a', '') |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.04 sec)
mysql> SELECT INSTR('1234a56789a', null);
+----------------------------+
| instr('1234a56789a', null) |
+----------------------------+
| NULL |
+----------------------------+
1 row in set (0.03 sec)
mysql> SELECT INSTR('1234a56789a', 'a', 1);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'instr'
mysql> SELECT INSTR('1234a56789a', 'a', 1, 1);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'instr'
LOCATE(substr,str), LOCATE(substr,str,pos)
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_locate
mysql> SELECT LOCATE('a', '1234a56789a');
+----------------------------+
| locate('a', '1234a56789a') |
+----------------------------+
| 5 |
+----------------------------+
1 row in set (0.08 sec)
mysql> SELECT LOCATE('a', '1234a56789a', 2);
+-------------------------------+
| locate('a', '1234a56789a', 2) |
+-------------------------------+
| 5 |
+-------------------------------+
1 row in set (0.05 sec)
mysql> SELECT LOCATE('a', '1234a56789a', 5);
+-------------------------------+
| locate('a', '1234a56789a', 5) |
+-------------------------------+
| 5 |
+-------------------------------+
1 row in set (0.08 sec)
mysql> SELECT LOCATE('a', '1234a56789a', 6);
+-------------------------------+
| locate('a', '1234a56789a', 6) |
+-------------------------------+
| 11 |
+-------------------------------+
1 row in set (0.09 sec)
mysql> SELECT LOCATE('a', '1234a56789a', -1);
+--------------------------------+
| locate('a', '1234a56789a', -1) |
+--------------------------------+
| 0 |
+--------------------------------+
1 row in set (0.35 sec)
mysql> SELECT LOCATE('a', '1234a56789a', 1);
+-------------------------------+
| locate('a', '1234a56789a', 1) |
+-------------------------------+
| 5 |
+-------------------------------+
1 row in set (0.13 sec)
mysql> SELECT LOCATE('a', '1234a56789a', 0);
+-------------------------------+
| locate('a', '1234a56789a', 0) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (0.35 sec)
PostgreSQL
42.13.3. 付録 本節には、移植作業を簡略化するために使用できる、Oracle互換のinstr関数のコードがあります。
https://www.postgresql.jp/document/12/html/plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX
INSTR()はないのですが、類似関数として以下があるようです。また、position()と言う関数もあります。ですが、どちらもpositionやoccurrenceといった引数はない。マニュアルにOracleからの移植作業向けUDFの解説がある点は興味深いところ。
strpos(string, substring)
https://www.postgresql.jp/document/7.4/html/functions-string.html
postgres=> SELECT STRPOS('1234a56789a', 'a');
strpos
--------
5
(1 row)
postgres=> SELECT STRPOS('1234a56789a', '');
strpos
--------
1
(1 row)
postgres=> \pset null [NULL]
Null display is "[NULL]".
postgres=> SELECT STRPOS('1234a56789a', NULL);
strpos
--------
[NULL]
(1 row)
position(substring in string)
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_POSITION.html
postgres=> SELECT POSITION('a' in '1234a56789a');
position
----------
5
(1 row)
postgres=> SELECT POSITION('' in '1234a56789a');
position
----------
1
(1 row)
postgres=> SELECT POSITION(NULL in '1234a56789a');
position
----------
[NULL]
(1 row)
postgres=> SELECT POSITION(0 in '1234a56789a');
ERROR: function pg_catalog.position(unknown, integer) does not exist
LINE 1: SELECT POSITION(0 in '1234a56789a');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
postgres=>
Redshift
PostgreSQL系の流れをくむRedshiftに、CHARINDEX()なる関数がある。同名の関数名が見つかるのはSQL Serverですね。それはそれで興味深い。
STRPOS(string, substring )
CHARINDEX( substring, string )
POSITION(substring IN string )
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_POSITION.html
実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺） Advent Calendar 2019
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
| 固定リンク | 0
コメント