SQL UDF replace_chars

Extensibility
Teradata Employee

SQL UDF replace_chars

I put together a simple sql udf to replace characters in a string. Its limited to a 55 character source string but works well.

select replace_chars( '1%223456#664', '%16#','2')

Result:

222234522224

REPLACE FUNCTION SYSLIB.replace_chars(src_str varchar(55),chars_to_rep varchar(25),rep_char varchar(10) )

  RETURNS VARCHAR(255)

  SPECIFIC SYSLIB.replace_chars

  RETURNS NULL ON NULL INPUT

  CONTAINS SQL

  COLLATION INVOKER

  INLINE TYPE 1

 RETURN  

TRIM((CASE WHEN POSITION(SUBSTRING( src_str FROM 1 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=1 THEN rep_char ELSE SUBSTRING( src_str FROM  1 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 2 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=2 THEN rep_char ELSE SUBSTRING( src_str FROM  2 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 3 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=3 THEN rep_char ELSE SUBSTRING( src_str FROM  3 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 4 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=4 THEN rep_char ELSE SUBSTRING( src_str FROM  4 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 5 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=5 THEN rep_char ELSE SUBSTRING( src_str FROM  5 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 6 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=6 THEN rep_char ELSE SUBSTRING( src_str FROM  6 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 7 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=7 THEN rep_char ELSE SUBSTRING( src_str FROM  7 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 8 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=8 THEN rep_char ELSE SUBSTRING( src_str FROM  8 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 9 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=9 THEN rep_char ELSE SUBSTRING( src_str FROM  9 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 10 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=10 THEN rep_char ELSE SUBSTRING( src_str FROM  10 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 11 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=11 THEN rep_char ELSE SUBSTRING( src_str FROM  11 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 12 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=12 THEN rep_char ELSE SUBSTRING( src_str FROM  12 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 13 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=13 THEN rep_char ELSE SUBSTRING( src_str FROM  13 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 14 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=14 THEN rep_char ELSE SUBSTRING( src_str FROM  14 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 15 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=15 THEN rep_char ELSE SUBSTRING( src_str FROM  15 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 16 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=16 THEN rep_char ELSE SUBSTRING( src_str FROM  16 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 17 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=17 THEN rep_char ELSE SUBSTRING( src_str FROM  17 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 18 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=18 THEN rep_char ELSE SUBSTRING( src_str FROM  18 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 19 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=19 THEN rep_char ELSE SUBSTRING( src_str FROM  19 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 20 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=20 THEN rep_char ELSE SUBSTRING( src_str FROM  20 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 21 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=21 THEN rep_char ELSE SUBSTRING( src_str FROM  21 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 22 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=22 THEN rep_char ELSE SUBSTRING( src_str FROM  22 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 23 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=23 THEN rep_char ELSE SUBSTRING( src_str FROM  23 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 24 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=24 THEN rep_char ELSE SUBSTRING( src_str FROM  24 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 25 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=25 THEN rep_char ELSE SUBSTRING( src_str FROM  25 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 26 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=26 THEN rep_char ELSE SUBSTRING( src_str FROM  26 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 27 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=27 THEN rep_char ELSE SUBSTRING( src_str FROM  27 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 28 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=28 THEN rep_char ELSE SUBSTRING( src_str FROM  28 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 29 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=29 THEN rep_char ELSE SUBSTRING( src_str FROM  29 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 30 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=30 THEN rep_char ELSE SUBSTRING( src_str FROM  30 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 31 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=31 THEN rep_char ELSE SUBSTRING( src_str FROM  31 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 32 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=32 THEN rep_char ELSE SUBSTRING( src_str FROM  32 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 33 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=33 THEN rep_char ELSE SUBSTRING( src_str FROM  33 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 34 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=34 THEN rep_char ELSE SUBSTRING( src_str FROM  34 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 35 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=35 THEN rep_char ELSE SUBSTRING( src_str FROM  35 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 36 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=36 THEN rep_char ELSE SUBSTRING( src_str FROM  36 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 37 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=37 THEN rep_char ELSE SUBSTRING( src_str FROM  37 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 38 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=38 THEN rep_char ELSE SUBSTRING( src_str FROM  38 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 39 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=39 THEN rep_char ELSE SUBSTRING( src_str FROM  39 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 40 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=40 THEN rep_char ELSE SUBSTRING( src_str FROM  40 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 41 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=41 THEN rep_char ELSE SUBSTRING( src_str FROM  41 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 42 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=42 THEN rep_char ELSE SUBSTRING( src_str FROM  42 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 43 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=43 THEN rep_char ELSE SUBSTRING( src_str FROM  43 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 44 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=44 THEN rep_char ELSE SUBSTRING( src_str FROM  44 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 45 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=45 THEN rep_char ELSE SUBSTRING( src_str FROM  45 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 46 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=46 THEN rep_char ELSE SUBSTRING( src_str FROM  46 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 47 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=47 THEN rep_char ELSE SUBSTRING( src_str FROM  47 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 48 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=48 THEN rep_char ELSE SUBSTRING( src_str FROM  48 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 49 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=49 THEN rep_char ELSE SUBSTRING( src_str FROM  49 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 50 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=50 THEN rep_char ELSE SUBSTRING( src_str FROM  50 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 51 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=51 THEN rep_char ELSE SUBSTRING( src_str FROM  51 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 52 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=52 THEN rep_char ELSE SUBSTRING( src_str FROM  52 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 53 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=53 THEN rep_char ELSE SUBSTRING( src_str FROM  53 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 54 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=54 THEN rep_char ELSE SUBSTRING( src_str FROM  54 FOR 1) END)||

(CASE WHEN POSITION(SUBSTRING( src_str FROM 55 FOR 1) IN chars_to_rep) <> 0 AND char_length(src_str) >=55 THEN rep_char ELSE SUBSTRING( src_str FROM  55 FOR 1) END));

Tags (1)