MySQL字符串的拼接、截取、替换、查找位置

MySQL字符串的拼接、截取、替换、查找位置。

常用的字符串函数:

函数说明
CONCAT(s1,s2,...)返回连接参数产生的字符串,一个或多个待拼接的内容,任意一个为NULL则返回值为NULL。
CONCAT_WS(x,s1,s2,...)返回多个字符串拼接之后的字符串,每个字符串之间有一个x。
SUBSTRING(s,n,len)、MID(s,n,len)两个函数作用相同,从字符串s中返回一个第n个字符开始、长度为len的字符串。
LEFT(s,n)、RIGHT(s,n)前者返回字符串s从最左边开始的n个字符,后者返回字符串s从最右边开始的n个字符。
INSERT(s1,x,len,s2)返回字符串s1,其子字符串起始于位置x,被字符串s2取代len个字符。
REPLACE(s,s1,s2)返回一个字符串,用字符串s2替代字符串s中所有的字符串s1。
LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)三个函数作用相同,返回子字符串str1在字符串str中的开始位置(从第几个字符开始)。
FIELD(s,s1,s2,...)返回第一个与字符串s匹配的字符串的位置。

 

1、字符串的拼接

1.1 CONCAT(s1,s2,...)函数

返回连接参数产生的字符串,一个或多个待拼接的内容,任意一个为NULL则返回值为NULL。

SELECT CONCAT('现在的时间:',NOW());  -- 输出结果:现在的时间:2019-01-17 11:27:58

1.2 CONCAT_WS(x,s1,s2,...)函数

返回多个字符串拼接之后的字符串,每个字符串之间有一个x。

SELECT CONCAT_WS(';','pan_junbiao的博客','KevinPan','pan_junbiao'); -- 输出结果:pan_junbiao的博客;KevinPan;pan_junbiao

 

2、字符串的截取

2.1 SUBSTRING(s,n,len)、MID(s,n,len)函数

两个函数作用相同,从字符串s中返回一个第n个字符开始、长度为len的字符串。

SELECT SUBSTRING('您好,欢迎访问pan_junbiao的博客',8,14);  -- 输出结果:pan_junbiao的博客
SELECT MID('您好,欢迎访问pan_junbiao的博客',8,14);        -- 输出结果:pan_junbiao的博客

2.2 LEFT(s,n)、RIGHT(s,n)函数

前者返回字符串s从最左边开始的n个字符,后者返回字符串s从最右边开始的n个字符。

SELECT LEFT('您好,欢迎访问pan_junbiao的博客',7);   -- 输出结果:您好,欢迎访问
SELECT RIGHT('您好,欢迎访问pan_junbiao的博客',14); -- 输出结果:pan_junbiao的博客

 

3、字符串的替换

3.1 INSERT(s1,x,len,s2)函数

返回字符串s1,其子字符串起始于位置x,被字符串s2取代len个字符。

SELECT INSERT('您好,欢迎访问阿标的博客',8,2,'pan_junbiao');  -- 输出结果:您好,欢迎访问pan_junbiao的博客

3.2 REPLACE(s,s1,s2)函数

返回一个字符串,用字符串s2替代字符串s中所有的字符串s1。

SELECT REPLACE('您好,欢迎访问阿标的博客','阿标','pan_junbiao'); -- 输出结果:您好,欢迎访问pan_junbiao的博客

 

4、字符串的查询位置

4.1 LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)函数

三个函数作用相同,返回子字符串str1在字符串str中的开始位置(从第几个字符开始)。

SELECT LOCATE('pan_junbiao','您好,欢迎访问pan_junbiao的博客');       -- 输出结果:8
SELECT POSITION('pan_junbiao' IN '您好,欢迎访问pan_junbiao的博客');  -- 输出结果:8
SELECT INSTR('您好,欢迎访问pan_junbiao的博客','pan_junbiao');        -- 输出结果:8

4.2 FIELD(s,s1,s2,...)函数

返回第一个与字符串s匹配的字符串的位置。

SELECT FIELD('pan_junbiao','pan_junbiao的博客','KevinPan','阿标','pan_junbiao');  -- 输出结果:4

 

<think>我们面对的问题是从MySQL字符串中提取纯数字部分。例如,字符串"abc123def456"需要提取为"123456"。 在MySQL中,没有内置函数可以直接删除所有非数字字符,但我们可以通过自定义函数或使用REGEXP_REPLACE(如果MySQL版本支持)来实现。 注意:MySQL 8.0+ 支持正则表达式函数REGEXP_REPLACE。对于低版本,我们可以尝试使用递归或循环的方式,但较为复杂。 方案1:使用REGEXP_REPLACE(MySQL 8.0+) ```sql SELECT REGEXP_REPLACE('abc123def456', '[^0-9]', '') AS numbers; ``` 解释:使用正则表达式替换所有非数字字符([^0-9])为空字符串。 方案2:对于不支持正则表达式的旧版本MySQL,我们可以创建一个自定义函数。下面是一个示例函数,它遍历字符串拼接数字字符: ```sql DELIMITER $$ CREATE FUNCTION extract_number(str VARCHAR(255)) RETURNS VARCHAR(255) BEGIN DECLARE i INT DEFAULT 1; DECLARE result VARCHAR(255) DEFAULT ''; IF str IS NULL THEN RETURN NULL; END IF; WHILE i <= LENGTH(str) DO IF SUBSTRING(str, i, 1) BETWEEN '0' AND '9' THEN SET result = CONCAT(result, SUBSTRING(str, i, 1)); END IF; SET i = i + 1; END WHILE; RETURN result; END$$ DELIMITER ; ``` 然后我们可以这样使用: ```sql SELECT extract_number('abc123def456') AS numbers; ``` 但是,请注意,自定义函数可能会影响性能,特别是在处理大量数据时。 另外,如果字符串中可能包含多个数字部分,而我们希望提取的是连续的数字(例如只提取第一次出现的连续数字),那么上述两种方法的行为是不同的: - 方案1(REGEXP_REPLACE)会删除所有非数字字符,因此会将所有数字拼接在一起。 - 自定义函数同样也是拼接所有数字。 如果我们希望提取第一次出现的连续数字,可以使用SUBSTRING和正则表达式(MySQL 8.0+): ```sql SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+') AS first_number; ``` 这将返回'123'。 因此,根据需求选择合适的方法。 注意:在MySQL 5.7及以下版本,不支持REGEXP_REPLACE和REGEXP_SUBSTR,所以可能需要使用自定义函数来提取连续数字或全部数字。 总结: - 如果MySQL版本>=8.0,使用正则表达式函数是最简洁的。 - 如果版本较低,且需要提取所有数字,可以使用自定义函数(遍历字符串)。 - 如果只需要第一次出现的连续数字,也可以在自定义函数中实现:遇到第一个连续数字串就返回(需要修改函数逻辑)。 下面是一个提取第一个连续数字串的自定义函数示例: ```sql DELIMITER $$ CREATE FUNCTION extract_first_number(str VARCHAR(255)) RETURNS VARCHAR(255) BEGIN DECLARE i INT DEFAULT 1; DECLARE result VARCHAR(255) DEFAULT ''; DECLARE in_number BOOL DEFAULT FALSE; DECLARE current_char CHAR(1); IF str IS NULL THEN RETURN NULL; END IF; WHILE i <= LENGTH(str) DO SET current_char = SUBSTRING(str, i, 1); IF current_char BETWEEN '0' AND '9' THEN SET result = CONCAT(result, current_char); SET in_number = TRUE; ELSE IF in_number THEN -- 已经进入过数字,现在遇到非数字,结束 RETURN result; END IF; END IF; SET i = i + 1; END WHILE; RETURN result; END$$ DELIMITER ; ``` 使用: ```sql SELECT extract_first_number('abc123def456') AS first_number; -- 返回'123' ``` 请根据实际需求选择方法。 相关问题: 1. 在MySQL中如何判断一个字符串是否包含数字? 2. 如何提取字符串中的最后一个连续数字串? 3. 在MySQL中如何替换字符串中的非数字字符为指定字符? 4. 在低版本MySQL中如何实现正则表达式类似的功能? 注意:在实际使用自定义函数时,要考虑到性能问题,特别是在大数据表上使用。</think>### MySQL字符串提取纯数字方法 在MySQL中提取字符串中的纯数字部分,根据版本不同有以下解决方案: #### ✅ 方法1:MySQL 8.0+ 正则表达式(推荐) ```sql -- 删除所有非数字字符 SELECT REGEXP_REPLACE('abc123def456!', '[^0-9]', '') AS numbers; -- 结果: 123456 -- 提取第一组连续数字 SELECT REGEXP_SUBSTR('订单号: 2023-456', '[0-9]+') AS first_num; -- 结果: 2023 ``` #### ✅ 方法2:自定义函数(兼容所有版本) ```sql DELIMITER $$ CREATE FUNCTION extract_numbers(input VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC BEGIN DECLARE output VARCHAR(255) DEFAULT ''; DECLARE pos INT DEFAULT 1; DECLARE len INT; DECLARE char CHAR(1); SET len = CHAR_LENGTH(input); WHILE pos <= len DO SET char = SUBSTRING(input, pos, 1); IF char BETWEEN '0' AND '9' THEN SET output = CONCAT(output, char); END IF; SET pos = pos + 1; END WHILE; RETURN output; END$$ DELIMITER ; -- 使用示例 SELECT extract_numbers('Tel: 400-881-1234') AS phone_num; -- 结果: 4008811234 ``` #### ✅ 方法3:无函数简单查询(有限场景) ```sql -- 仅当数字在固定位置时有效 SELECT SUBSTRING('ID: ABC123', 7) AS manual_extract; -- 结果: 123 ``` #### ⚠️ 性能注意事项 1. **大数据量时**:正则和自定义函数可能较慢,建议在应用层处理 2. **索引优化**:若常需提取数字,建议存储时分离数字列并建索引[^2] 3. **版本兼容**: - MySQL 5.x 必须用自定义函数 - MariaDB 10.0.5+ 支持`REGEXP_REPLACE` > 关键点:`REGEXP_REPLACE('[^0-9]', '')` 通过正则匹配非数字字符并替换为空字符串,是最简洁的解决方案[^1]。 --- ###
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

pan_junbiao

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值