Remove percentages and syntax from string in select statement

Question

I have data in a column with strings with syntax such as 'Consumer%20DNO%20CA'

I recall that %20 means a space in browsers.

Is there a simple way to select this field in a particular way so as to return 'Consumer DNO CA'

As opposed to regex I wondered if there was a command I could specify?

Note I'm an end user analyst and did not create the tables and db. I wondered if there was a function to decode this style of syntax. Perhaps I could find out what the syntax is first by selecting something from the table info tables?

It's encoded string. Better decode it in application level after selecting from mysql. — 1000111, Mar 21 '16 at 05:11
I don't think there's anything in MySQL that will do this. You need to move out a level and use something like PHP's `urldecode()` function. — Darwin von Corax, Mar 21 '16 at 05:12

xmarcos · Accepted Answer · 2016-03-21T06:10:04.957

There is nourl_decode function available in MySQL but you can easily REPLACE the most common escape characters if you want to stay mysql-only and don't have access to create a stored procedure.

The following example includes the reserved characters from RFC3986 plus some additional common characters.

SELECT
    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(
        'Consumer%20DNO%20CA', -- replace with proper column
        '%20',' '),
        '%21', '!'),
        '%22', '"'),
        '%23', '#'),
        '%24', '$'),
        '%25', '%'), 
        '%26', '&'),
        '%27', '\''),
        '%28', '('),
        '%29', ')'),
        '%2A', '*'),
        '%2B', '+'),
        '%2C', ','),
        '%2D', '-'),
        '%2E', '.'),
        '%2F', '/'),
        '%3A', ':'),
        '%3B', ';'),
        '%3C', '<'),
        '%3D', '='),
        '%3E', '>'),
        '%3F', '?'),
        '%40', '@'),
        '%5B', '['), 
        '%5D', ']'), 
        '%5E', '^'), 
        '%7C', '|'), 
        '%7E', '~'), 
        '%80', '`') as url_decoded;

Remove percentages and syntax from string in select statement

1 Answers1