Removing unwanted character from column

Question

I would like to remove the character '�' from column

Column Name:

asds�dfgdfg

dfgwer�werwer

And Want to replace it with space

Column Name:

asds dfgdfg

dfgwer werwer

I want to replace that character with space using update query — Nav Ali, Feb 14 '11 at 11:10
More often than not the � character isn't actually in your DB. It just means your software, using a specified character set, encountered an unrecognizable sequence of bytes in that charset, skipped processing and returned a � character to you. — Brian Duncan, May 06 '14 at 21:31
see "black diamond" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored It does not talk about removing, but about preventing. — Rick James, May 28 '22 at 19:15

score 51 · Accepted Answer · answered Feb 14 '11 at 11:24

That is a Unicode replacement character. If this character is appearing in your table then it might be that you are issuing queries using the wrong character set. You should check the column character set, and you should also check the character set(s) of the connection(s) you use to issue queries. If there is a difference in connection character set between connections used to read and record data, or if there is a difference in expected character set between applications/scripts used to access the data, that would explain the presence of these characters.

If you just want to replace it with a space:

UPDATE myTable SET myColumn = REPLACE(myColumn, '�', ' ')

Just a note, be aware that sometimes you may have to first escape the character that you want replaced "\" : `UPDATE myTable SET myColumn = REPLACE(myColumn, '\\', '')` — Ray, Dec 12 '12 at 16:51

score 16 · Answer 2 · answered Feb 11 '15 at 08:15

Replace below characters

~ ! @ # $ % ^ & * ( ) _ +
` - = 
{ } |
[ ] \
: " 
; '

< > ?
, .

with this SQL

SELECT note as note_original, 

    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(
                                            REPLACE(
                                                REPLACE(
                                                    REPLACE(
                                                        REPLACE(
                                                            REPLACE(
                                                                REPLACE(
                                                                    REPLACE(
                                                                        REPLACE(
                                                                            REPLACE(
                                                                                REPLACE(
                                                                                    REPLACE(
                                                                                        REPLACE(
                                                                                            REPLACE(
                                                                                                REPLACE(
                                                                                                    REPLACE(
                                                                                                        REPLACE(
                                                                                                            REPLACE(
                                                                                                                REPLACE(
                                                                                                                    REPLACE(
                                                                                                                        REPLACE(
                                                                                                                            REPLACE(
                                                                                                                                REPLACE(
                                                                                                                                    REPLACE(note, '"', ''),
                                                                                                                                '.', ''),
                                                                                                                            '?', ''),
                                                                                                                        '`', ''),
                                                                                                                    '<', ''),
                                                                                                                '=', ''),
                                                                                                            '{', ''),
                                                                                                        '}', ''),
                                                                                                    '[', ''),
                                                                                                ']', ''),
                                                                                            '|', ''),
                                                                                        '\'', ''),
                                                                                    ':', ''),
                                                                                ';', ''),
                                                                            '~', ''),
                                                                        '!', ''),
                                                                    '@', ''),
                                                                '#', ''),
                                                            '$', ''),
                                                        '%', ''),
                                                    '^', ''),
                                                '&', ''),
                                            '*', ''),
                                        '_', ''),
                                    '+', ''),
                                ',', ''),
                            '/', ''),
                        '(', ''),
                    ')', ''),
                '-', ''),
            '>', ''),
        ' ', '-'),
    '--', '-') as note_changed FROM invheader

I fell trying to read this and hit every parenthesis on the way down. — jamzsabb, Mar 15 '18 at 16:02

score -1 · Answer 3 · edited Oct 06 '18 at 12:27

-1

Use this query to change the charset: SET CHARSET 'utf8';

edited Oct 06 '18 at 12:27

sjas

18,644
14
87
92

answered Oct 09 '13 at 10:18

Praveen D

2,337
2
31
43

score -1 · Answer 4 · answered Oct 09 '13 at 10:22

-1

Execute below query to set charset

SET CHARSET 'utf8';
set names 'utf8'

answered Oct 09 '13 at 10:22

Praveen D

2,337
2
31
43

score -1 · Answer 5 · edited May 26 '22 at 20:52

-1

You Can Try this for multiple column

 UPDATE myTable
SET    myColumn1 = Replace(myColumn1, '�', ' '),
       myColumn2 = Replace(myColumn2, '�', ' '),
       myColumn3 = Replace(myColumn3, '�', ' '),
       ...;

edited May 26 '22 at 20:52

RF1991

2,037
4
8
17

answered Feb 05 '16 at 11:54

Nikhil Srivastava

1

1

This doesn't work. � if a representation of an unmapped character, it's not the REAL character. – fusion3k Feb 05 '16 at 12:02

Removing unwanted character from column

5 Answers5

Linked