How to speed up string cleanup function?
1
vote
3
answers
1449
views
I need to cleanup a string, so that certain ASCII code characters are left out of the string, and others are replaced.
I am new to Postgres. My function
ufn_cie_easy()
performs way too slow:
DECLARE
letter char = '';
str_result TEXT = '';
x integer;
y integer;
asc_code int;
BEGIN
y:=1;
x:=char_length(arg);
LOOP
letter=substring(arg from y for 1);
asc_code=ascii(letter);
IF (asc_code BETWEEN 47 and 58) or (asc_code BETWEEN 65 and 90) or (
asc_code BETWEEN 97 and 122) THEN
str_result := str_result || letter;
ELSIF (asc_code BETWEEN 192 and 197) THEN
str_result := str_result || 'A';
ELSIF (asc_code BETWEEN 200 and 203) THEN
str_result := str_result || 'E';
ELSIF (asc_code BETWEEN 204 and 207) THEN
str_result := str_result || 'I';
ELSIF (asc_code BETWEEN 210 and 214) OR (asc_code=216) THEN
str_result := str_result || 'O';
ELSIF (asc_code BETWEEN 217 and 220) THEN
str_result := str_result || 'U';
ELSIF (asc_code BETWEEN 224 and 229) THEN
str_result := str_result || 'a';
ELSIF (asc_code BETWEEN 232 and 235) THEN
str_result := str_result || 'e';
ELSIF (asc_code BETWEEN 236 and 239) THEN
str_result := str_result || 'i';
ELSIF (asc_code BETWEEN 242 and 246) OR (asc_code=248) THEN
str_result := str_result || 'o';
ELSIF (asc_code BETWEEN 249 and 252) THEN
str_result := str_result || 'u';
ELSE
CASE asc_code
WHEN 352 THEN str_result := str_result || 'S';
WHEN 338 THEN str_result := str_result || 'OE';
WHEN 381 THEN str_result := str_result || 'Z';
WHEN 353 THEN str_result := str_result || 's';
WHEN 339 THEN str_result := str_result || 'oe';
WHEN 382 THEN str_result := str_result || 'z';
WHEN 162 THEN str_result := str_result || 'c';
WHEN 198 THEN str_result := str_result || 'AE';
WHEN 199 THEN str_result := str_result || 'C';
WHEN 208 THEN str_result := str_result || 'D';
WHEN 209 THEN str_result := str_result || 'N';
WHEN 223 THEN str_result := str_result || 'ss';
WHEN 230 THEN str_result := str_result || 'ae';
WHEN 231 THEN str_result := str_result || 'c';
WHEN 241 THEN str_result := str_result || 'n';
WHEN 376 THEN str_result := str_result || 'Y';
WHEN 221 THEN str_result := str_result || 'Y';
WHEN 253 THEN str_result := str_result || 'y';
WHEN 255 THEN str_result := str_result || 'y';
ELSE str_result := str_result;
END CASE;
END IF;
y:=y+1;
exit when y=x+1;
END LOOP;
return str_result;
END;
Asked by W. Smets
(11 rep)
Jul 28, 2016, 08:04 AM
Last activity: May 26, 2024, 09:06 PM
Last activity: May 26, 2024, 09:06 PM