select INV_NO,abc.REGEXP_LIKE(TRIM(coalesce(INV_NO_ ‘0’)), ‘^[0]{1,}$’)
,abc.REGEXP_LIKE(coalesce(INV_NO,‘0’), ‘^\s+$’)
from abc_wbm.wbminv where inv_no= ‘3268145’
This must return false but its returning true after i changed
from v_like_flag := EXISTS(SELECT regexp_matches(src_string, regexp_pat,
to v_like_flag := EXISTS(SELECT regexp_match(src_string, regexp_pat,
Chinese characters accepting but its failing in the above scenario.
select abcd.FNC_VALIDATE_ALPHA_NUM(‘武汉市’::text, 0::double PRECISION);
CREATE OR REPLACE FUNCTION abc.regexp_like(
src_string text,
regexp_pat text,
p_match_param text DEFAULT 'c'::text)
RETURNS boolean
LANGUAGE 'plpgsql'
COST 100
IMMUTABLE PARALLEL UNSAFE
AS $BODY$
DECLARE
v_match_param TEXT;
v_like_flag BOOLEAN := FALSE;
BEGIN
-- Removing spaces from match_param
v_match_param := trim(p_match_param);
-- Block of input parameters validation checks
IF (coalesce(src_string, '') = '' OR
coalesce(regexp_pat, '') = '')
THEN
RETURN v_like_flag;
ELSIF (coalesce(v_match_param, '') = '') THEN
v_match_param := 'c';
ELSIF (v_match_param !~ 'i|c|n|m|x') THEN
/* The value of the argument for parameter in position "3" (match parameter)
must be one of the following: "i", "c", "n", "m", "x" */
RAISE USING MESSAGE := 'Illegal argument for function.',
DETAIL := 'The value of the argument must be one of the following: "i", "c", "n", "m", "x".',
HINT := '-1760';
END IF;
-- Translate regexp flags (match parameter) between matching engines
v_match_param := concat('g', v_match_param);
v_match_param := regexp_replace(v_match_param, 'm|x', '', 'g');
v_match_param := CASE
WHEN v_match_param !~ 'n' THEN v_match_param || 'p'
ELSE regexp_replace(v_match_param, 'n', '', 'g')
END;
-- Finding if there is a match regarding supplied regexp mask
v_like_flag := EXISTS(SELECT regexp_matches(src_string, regexp_pat, v_match_param));
RETURN v_like_flag;
END;
$BODY$;
COMMENT ON FUNCTION abc.regexp_like(text, text, text)
IS 'This function returns a boolean value indicating if the regular expression pattern is found in a string.';
@pogomips @castorp Any help?