等价Sybase的patindex函数

Tags: /PostgreSQL/ Date Created:

Sybase的patindex函数，用于返回通配符字符串在目标字符串中的开始位置，例如：

-- 返回2，因‘%hoco%’匹配整个字符串，hoco从第2个位置开始

SELECT PATINDEX( '%hoco%', 'chocolate' )

-- 返回11，因‘%4_5_’匹配整个字符串，4_5_从第11个位置开始

SELECT PATINDEX ('%4_5_', '0a1A 2a3A 4a5A' )

-- 返回0，因'0a1A 2a3A 4a5A'不从4开始:

SELECT PATINDEX ('4_5_', '0a1A 2a3A 4a5A' )

-- 返回0，因'abc-123-def'不以数字结束:

SELECT PATINDEX ('%[0-9]', 'abc-123-def')

-- 返回5，因'123'从第5个位置开始:

SELECT PATINDEX ('%[0-9]%', 'abc-123-def')

-- 返回1，因'abc-123-def'第一个字符就不为数字:

SELECT PATINDEX ('%[^0-9]%', 'abc-123-def')

PostgreSQL中，没有类似函数，我们可以自己建立一个如下的自定义函数：

CREATE OR REPLACE FUNCTION patindex( pattern VARCHAR, expression VARCHAR ) 
RETURNS INT AS $$
DECLARE v_i int;
v_pattern varchar; 
BEGIN 
 
v_pattern:=TRIM( pattern, '%' );
IF left(pattern, 1)!='%' THEN
v_pattern:='^'||v_pattern;
END IF;
IF right(pattern, 1)!='%' THEN
v_pattern:= v_pattern||'$';
END IF;
 
SELECT COALESCE(
   STRPOS( $2
       ,(  SELECT
               ( REGEXP_MATCHES( $2
                   ,'(' || REPLACE( REPLACE( v_pattern, '%', '.*?' ), '_', '.' ) || ')'
               ) )[ 1 ]
           LIMIT 1
       )
   )
   ,0
) INTO v_i;  
RETURN v_i;
END;
$$ LANGUAGE plpgsql IMMUTABLE;