不同数据库中实现字符串按字符分割后取前几项

概述

在不同数据库中，字符串按字符分割后取前几项（去掉最后一项）的实现方法。

例：str=’/data/pg_data/pg_15/data’，res=’/data/pg_data/pg_15’

实现

pg

[postgres@mydb1a log]$ psql
psql (17beta1, server 15.0)
Type "help" for help.

postgres=# select
postgres-#   a.dd,
postgres-#   length(a.dd) - length(replace(a.dd, '/', '')) len,
postgres-#   array_to_string(
postgres(#     (string_to_array(a.dd, '/')) [ : (length(a.dd) - length(replace(a.dd, '/', ''))) ],
postgres(#     '/'
postgres(#   )
postgres-# from
postgres-#   (
postgres(#     select
postgres(#       '/data/pg_data/pg15/data' as dd
postgres(#   ) a;
           dd            | len |  array_to_string
-------------------------+-----+--------------------
 /data/pg_data/pg15/data |   4 | /data/pg_data/pg15
(1 row)

mogdb

[omm@mydb1b ~]$ gsql -r
gsql ((MogDB 5.0.6 build 8b0a6ca8) compiled at 2024-03-27 11:05:30 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=# select
MogDB-#   a.dd,
MogDB-#   length(a.dd) - length(replace(a.dd, '/', '')) len,
MogDB-#   array_to_string(
MogDB(#     (string_to_array(a.dd, '/')) [ 1: (length(a.dd) - length(replace(a.dd, '/', ''))) ],
MogDB(#     '/'
MogDB(#   )
MogDB-# from
MogDB-#   (
MogDB(#     select
MogDB(#       '/data/pg_data/pg15/data' as dd
MogDB(#   ) a;
           dd            | len |  array_to_string
-------------------------+-----+--------------------
 /data/pg_data/pg15/data |   4 | /data/pg_data/pg15
(1 row)

mysql

oracle

总结

• pg和mogdb中，均可以通过数组和字符串互相转换的函数，并结合数组切片查询实现，区别是pg中数组切片时可以省略起始位置或结束位置，mogdb中则必须指定完整的范围
• MySQL中，使用substring_index函数返回给定分隔符第n次出现位置前的字符串
• oracle中，首先使用instr函数确定给定分隔符第n次出现在字符串的位置，再通过substr截取字符串
• 本次测试的mogdb5.0.6中，可以使用oracle中的语法运行，无需修改SQL，pg15.0可以使用regexp_instr函数模拟oracle中的实现方式
• 使用length和replace来获取分隔符个数的方法，在本次测试的数据库中使用方法一致