DROP FUNCTION IF EXISTS PatIndex;
DELIMITER $$
CREATE FUNCTION PatIndex(pattern VARCHAR(255), tblString VARCHAR(255)) RETURNS INTEGER
DETERMINISTIC
BEGIN
DECLARE i INTEGER;
SET i = 1;
myloop: WHILE (i <= LENGTH(tblString)) DO
IF SUBSTRING(tblString, i, 1) REGEXP pattern THEN
RETURN(i);
LEAVE myloop;
END IF;
SET i = i + 1;
END WHILE;
RETURN(0);
END
BEGIN
DECLARE i VARCHAR(255);
set i = CASE
WHEN instr(pups_no, '(')
AND instr(pups_no, ')') THEN
concat(
LEFT (
pups_no,
instr(pups_no, '(') - 1
),
substr(
pups_no,
instr(pups_no, ')') + 1
)
)
WHEN instr(pups_no, '(') THEN
LEFT (
pups_no,
instr(pups_no, '(') - 1
)
WHEN instr(pups_no, ')') THEN
substr(
pups_no,
instr(pups_no, ')') + 1
)
ELSE
pups_no
END;
return i;
END