pg>mysql>substring_index
2021-09-26 10:22:58    47    0    0
ivan

MySQL函数功能:

    按照关键字截取字符串,substring_index(被截取字段,关键字,关键字出现的次数)

PG建兼容函数:

create or replace function substring_index(text,text,int) 

returns text as $$

    declare

        a text;

        b text :='';

        var int;

    begin

        for var in 1..$3 loop

            a=split_part($1,$2,var);

            b=b || $2 || a;

        end loop;

    return substring(b from 2 for length(b));

    end

$$ language plpgsql

例子:

MySQL:

mysql> select substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3);
+--------------------------------------------------------------+
| substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3) |
+--------------------------------------------------------------+
| fhafjkdalsfhiad,fjadsoj;f,ad                                 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

PG:

postgres=# select substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3);
       substring_index
------------------------------
 fhafjkdalsfhiad,fjadsoj;f,ad
(1 行记录)

 

Pre: pg>游标及客户端查询

Next: pg>mysql>aggregate.sql

47
Sign in to leave a comment.
No Leanote account? Sign up now.
0 comments
Table of content