How to remove special characters from a string in mysql select query

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 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 remove a specific character from a string in MySQL?
REPLACE() function
MySQL REPLACE() replaces all the occurrences of a substring within a string. A string. A string which is present one or more times within the string str.
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.
How do I remove a character from a string in SQL query?
The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
Đọc tiếp







programming
mysql
MySQL multiple replace
Mysql TRIM characters
REPLACE in MySQL



Reply
2
0
 Chia sẻ