Replace trademark/copyright symbols in string

edited June 26 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 Employee

    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)
    
  • edited June 26

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

  • SruthiASruthiA Employee

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

  • Thank you so much!

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.