Replace trademark/copyright symbols in string

shashank_pshashank_p Vertica Customer
edited June 2020 in General Discussion

I have a column which contains data with copyright/trademark symbols, I need to find and remove it.
My text: solidworks®
Desired output: solidworks
Kindly suggest.

Tagged:

Best Answer

Answers

  • moshegmosheg Vertica Employee Administrator

    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)
    
  • shashank_pshashank_p Vertica Customer
    edited June 2020

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

  • SruthiASruthiA Vertica Employee Administrator

    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=>

  • shashank_pshashank_p Vertica Customer

    Thank you so much!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file