Sample Header Ad - 728x90

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