Replace special characters in mysql

There is no regular expression replacement. Use the following code to replace all special characters with '-'.

UPDATE  SET  = 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 (, '/', '-'), ',', '-'), '.', '-'), '<', '-'), '>', '-'), '?', '-'), ';', '-'), ':', '-'), '"', '-'), "'", '-'), '|', '-'), '\\', '-'), '=', '-'), '+', '-'), '*', '-'), '&', '-'), '^', '-'), '%', '-'), '$', '-'), '#', '-'), '@', '-'), '!', '-'), '~', '-'), '`', '-'), '', '-'), '{', '-' ), '}', '-' ), '[', '-' ), ']', '-' ), '(', '-' ), ')', '-' )
Code formatted 



UPDATE
    
SET
     =
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
                                                                                                                        (, '/', '-'),
                                                                                                                        ',',
                                                                                                                        '-'
                                                                                                                    ),
                                                                                                                    '.',
                                                                                                                    '-'
                                                                                                                ),
                                                                                                                '<',
                                                                                                                '-'
                                                                                                            ),
                                                                                                            '>',
                                                                                                            '-'
                                                                                                        ),
                                                                                                        '?',
                                                                                                        '-'
                                                                                                    ),
                                                                                                    ';',
                                                                                                    '-'
                                                                                                ),
                                                                                                ':',
                                                                                                '-'
                                                                                            ),
                                                                                            '"',
                                                                                            '-'
                                                                                        ),
                                                                                        "'",
                                                                                        '-'
                                                                                    ),
                                                                                    '|',
                                                                                    '-'
                                                                                ),
                                                                                '\\',
                                                                                '-'
                                                                            ),
                                                                            '=',
                                                                            '-'
                                                                        ),
                                                                        '+',
                                                                        '-'
                                                                    ),
                                                                    '*',
                                                                    '-'
                                                                ),
                                                                '&',
                                                                '-'
                                                            ),
                                                            '^',
                                                            '-'
                                                        ),
                                                        '%',
                                                        '-'
                                                    ),
                                                    '$',
                                                    '-'
                                                ),
                                                '#',
                                                '-'
                                            ),
                                            '@',
                                            '-'
                                        ),
                                        '!',
                                        '-'
                                    ),
                                    '~',
                                    '-'
                                ),
                                '`',
                                '-'
                            ),
                            '',
                            '-'
                        ),
                        '{',
                        '-'
                    ),
                    '}',
                    '-'
                ),
                '[',
                '-'
            ),
            ']',
            '-'
        ),
        '(',
        '-'
    ),
    ')',
    '-'
)



How do I change special characters in MySQL?
MySQL - How to include special characters in a query.
 
\0 - An ASCII NUL (0x00) character..
 
\' - A single quote ( ' ) character..
 
\" - A double quote ( " ) character..
 
\b - A backspace character..
 
\n - A newline (linefeed) character..
 
\r - A carriage return character..
 
\t - A tab character..
 
\Z - ASCII 26 (Control-Z)..
 
How do I replace a special character in SQL?
DECLARE @s varchar(20) = '&®™+•·()';.
 
SELECT..
 
REPLACE(@s, CHAR(38), SPACE(0)), -- &.
 
REPLACE(@s, CHAR(174), SPACE(0)), -- ®.
 
REPLACE(@s, CHAR(153), SPACE(0)), -- ™.
 
REPLACE(@s, CHAR(43), SPACE(0)), -- +.
 
REPLACE(@s, CHAR(149), SPACE(0)), -- •.
 
REPLACE(@s, CHAR(183), SPACE(0)), -- ·.
 
How do I remove special characters from a MySQL query?
Learn MySQL from scratch for Data Science and Analytics
You can remove special characters from a database field using REPLACE() function. The special characters are double quotes (“ “), Number sign (#), dollar sign($), percent (%) etc.
How do I exclude a special character in SQL query?
In some situations, we may not want a space or another special character, so we can include the special character that we want to exclude in our not regular expression, such as [^A-Z0-9 ] to exclude a space.
Đọc tiếp







programming
mysql
MySQL multiple replace
REPLACE in MySQL
Mysql TRIM characters



Reply
7
0
 Chia sẻ