---
title: "MySQL: Replacing URL Escape Sequences"
description: You want to store URLs in MySQL, and they have those symbols? And you want to show your users some kind of readable information. You should use this trick.
---

[Blog | Pythian ](https://www.pythian.com/blog)

# [MySQL: Replacing URL Escape Sequences](https://www.pythian.com/blog/mysql-replacing-url-escape-sequences)

 Written by [Pythian Marketing](https://www.pythian.com/blog/author/pythian-marketing) | Sep 26, 2008 4:00:00 AM

## Requirement

So you want to store URLs in MySQL, and the URLs have those annoying `%20%27%7C%26%5E%2B%2D%25` symbols? And you want to be able to show your users some kind of human-readable information. You might want to consider using this trick. Take this list of commonly escaped characters as an example:

```
%20 - space
%27 - '
%7C - |
%26 - &
%5E - ^
%2B - +
%2D - -
%25 - %
```

So, how about we do some search’n’replace on that?

```
mysql> SET @url:='%20%27%7C%26%5E%2B%2D%25';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @url as original,
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        @test,'%20',' '),
    ->        '%27','\''),
    ->        '%7C','|'),  -- REPLACE() is case sensitive
    ->        '%7c','|'),  -- so we have 
    ->        '%26','&'),
    ->        '%5E','^'),
    ->        '%5e','^'),  -- to repeat 
    ->        '%2D','-'),
    ->        '%2d','-'),  -- ourselves
    ->        '%2B','+'),
    ->        '%2b','+'),  -- sometimes
    ->        '%25','%') as replaced;
+--------------------------+----------+
| original                 | replaced |
+--------------------------+----------+
| %20%27%7C%26%5E%2B%2D%25 |  '|&^+-% | 
+--------------------------+----------+
1 row in set (0.01 sec)

mysql>
```

## We can easily turn this into a function:

```
mysql> CREATE DATABASE foo;
Query OK, 1 row affected (0.03 sec)

mysql> USE foo;
Database changed
mysql> CREATE FUNCTION url_replace 
    -> (url VARCHAR(1024))
    -> RETURNS VARCHAR(1024)
    -> BEGIN
    -> RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        @test,'%20',' '),
    ->        '%27','\''),
    ->        '%7C','|'),
    ->        '%7c','|'),
    ->        '%26','&'),
    ->        '%5E','^'),
    ->        '%5e','^'),
    ->        '%2D','-'),
    ->        '%2d','-'),
    ->        '%2B','+'),
    ->        '%2b','+'),
    ->        '%25','%');
    -> END$$
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;

mysql> SELECT url_replace('%20%27%7C%26%5E%2B%2D%25') as bar;
+----------+
| bar      |
+----------+
|  '|&^+-% | 
+----------+
1 row in set (0.00 sec)

mysql>
```

I hope this is useful to you guys. Cheers!

## Useful links:

- [MySQL Reference Manual :: String Functions :: REPLACE()](https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace)
- [HTML URL-encoding Reference](https://www.w3schools.com/TAGS/ref_urlencode.asp)

## MySQL Database Consulting Services

Ready to optimize your MySQL Database for the future?

[View full post](https://www.pythian.com/blog/mysql-replacing-url-escape-sequences)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Pythian Marketing"
  },
  "dateModified" : "2026-04-07T23:55:49.786Z",
  "datePublished" : "2008-09-26T04:00:00Z",
  "headline" : "MySQL: Replacing URL Escape Sequences",
  "image" : {
    "@type" : "ImageObject",
    "height" : 60,
    "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
    "width" : 60
  },
  "mainEntityOfPage" : "https://www.pythian.com/blog/mysql-replacing-url-escape-sequences",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Pythian Blog"
  }
}
```