Replace trademark/copyright symbols in string

Answers

• mosheg Vertica Employee Administrator

  June 2020

  We can do it with the exact string you want to replace as shown by default in f2
  Or find the asci value of it like so: printf "%d\n" "'®"
  Which is 174 and replace it like it is done by default in f3.

  cat replace.sql
  CREATE TABLE public.t1
  ( f1 varchar(50),
    f2 varchar(50) default REPLACE(f1,'®',''),
    f3 varchar(50) default REPLACE(f1,chr(174),'')
  );
  
  INSERT INTO t1(f1) VALUES ('solidworks®');
  INSERT INTO t1(f1) VALUES ('solid® works®');
  COMMIT;
  SELECT * FROM t1;
  ---------------------- Run time output:
  vsql -f replace.sql
        f1       |     f2      |     f3
  ---------------+-------------+-------------
   solidworks®   | solidworks  | solidworks
   solid® works® | solid works | solid works
  (2 rows)
  

  0
• shashank_p Vertica Customer ✭

  June 2020 edited June 2020

  Great! it worked.
  One question, how can I find asci value for such characters using DBeaver?

  0
• SruthiA Administrator

  June 2020

  Please use the below query to find the ascii values of trademark characters in DBeaver.

  dbadmin=> SELECT * FROM t1;
  f1 | f2 | f3
  ---------------+-------------+-------------
  solidworks® | solidworks | solidworks
  solid® works® | solid works | solid works
  (2 rows)

  dbadmin=> select ascii(substr(f1,length(f1),1)) from t1;

  ascii

  174
  174
  (2 rows)

  dbadmin=>

  1
• shashank_p Vertica Customer ✭

  June 2020

  Thank you so much!

  0