CODEWITHSUNDEEP
mysql
Nav Ali
Nav Ali

Reputation: 1230

Removing unwanted character from column

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

Upvotes: 24

Views: 63144

Answers (5)

Nikhil Srivastava
Nikhil Srivastava

Reputation: 1

You Can Try this for multiple column

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

Upvotes: -1

Praveen D
Praveen D

Reputation: 2377

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

Upvotes: -1

Umar Adil
Umar Adil

Reputation: 5277

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

Upvotes: 16

Praveen D
Praveen D

Reputation: 2377

Execute below query to set charset 

SET CHARSET 'utf8';
set names 'utf8'

Upvotes: -1

Hammerite
Hammerite

Reputation: 22340

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, '�', ' ')

Upvotes: 52

Related Questions