Want to Use TiDB to Convert Strings to URL Encoding

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 想使用tidb把字符串转url编码

| username: 灰太狼来了

[TiDB Usage Environment] Production Environment
[TiDB Version] 5.4.3
[Encountered Problem: Phenomenon and Impact]
I want to create a function that can convert a string to URL encoding. Currently, I have one for MySQL:

DELIMITER $$

CREATE FUNCTION urlencode(str VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
    DECLARE hex_chars VARCHAR(16) DEFAULT '0123456789ABCDEF';
    DECLARE encoded_str VARCHAR(255) DEFAULT '';
    DECLARE c VARCHAR(1);
    DECLARE i INT DEFAULT 1;

    WHILE i <= CHAR_LENGTH(str) DO
        SET c = SUBSTRING(str, i, 1);
        
        IF LOCATE(c, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-_.~') > 0 THEN
            SET encoded_str = CONCAT(encoded_str, c);
        ELSE
            SET encoded_str = CONCAT(encoded_str, '%', SUBSTRING(hex_chars, ORD(c) DIV 16 + 1, 1), SUBSTRING(hex_chars, ORD(c) MOD 16 + 1, 1));
        END IF;
        
        SET i = i + 1;
    END WHILE;

    RETURN encoded_str;
END $$

DELIMITER ;

SELECT urlencode('Hello World');

It shows the following:

Hello%20World

I want one for TiDB, or is there a built-in function that can achieve this?

2
| username: tidb菜鸟一只 | Original post link

tidb does not support functions, use python to implement it…

import urllib.parse

# The string to be converted
original_string = "Hello world"

# Convert the string to URL encoding
url_encoded_string = urllib.parse.quote(original_string)

# Print the result
print("Original string:", original_string)
print("URL encoded:", url_encoded_string)
3
| username: 灰太狼来了 | Original post link

It won’t work because we need to use Kettle to synchronize data, so we can only find a solution at the database level.

4
| username: tidb菜鸟一只 | Original post link

Where is the data synchronized to?

5
| username: 灰太狼来了 | Original post link

Syncing from an interface to the database, but the input parameters of that interface need to be converted to URL encoding. Since Kettle doesn’t support this, the idea is to use the database to convert to URL encoding and then pass it into the interface.

6
| username: tidb菜鸟一只 | Original post link

I don’t understand. Is TiDB the source end now? What is the target database?

7
| username: 灰太狼来了 | Original post link

It seems that Kettle supports URL encoding. I’ll implement it using Kettle. Thanks a lot.

8
| username: redgame | Original post link

Kettle is suitable for this. It’s a good choice.

9
| username: cassblanca | Original post link

TiDB does not support UDF functions or stored procedures. Use other scripting languages instead.

10
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.