Replace trademark/copyright symbols in string

shashank_p

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.

mosheg

In addition if you want to replace all values in f1 consider something like the following.

UPDATE public.t1 SET f1=REPLACE(f1,chr(174),'');
SELECT * FROM t1;
     f1      |     f2      |     f3
-------------+-------------+-------------
 solid works | solid works | solid works
 solidworks  | solidworks  | solidworks
(2 rows)

mosheg

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_p

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

SruthiA

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_p

Thank you so much!