sqlregexpreplace

⑴ mysql 正则表达式替换,把指定位置随机数替换成固定数字

MySQL 一直以来都支持正则匹配，不过对于正则替换则一直到MySQL 8.0 才支持。对于这类场景，以前要么在MySQL端处理，要么把数据拿出来在应用端处理。

比如我想把表y1的列str1的出现第3个action的子 串替换成dble，怎么实现？

1. 自己写SQL层的存储函数。代码如下写死了3个，没有优化，仅仅作为演示，MySQL 里非常不建议写这样的函数。

• mysql

• DELIMITER $$

• USE `ytt`$$

• DROP FUNCTION IF EXISTS `func_instr_simple_ytt`$$

• CREATE DEFINER=`root`@`localhost` FUNCTION `func_instr_simple_ytt`(

• f_str VARCHAR(1000), -- Parameter 1

• f_substr VARCHAR(100), -- Parameter 2

• f_replace_str varchar(100),

• f_times int -- times counter.only support 3.

• ) RETURNS varchar(1000)

• BEGIN

• declare v_result varchar(1000) default 'ytt'; -- result.

• declare v_substr_len int default 0; -- search string length.

• set f_times = 3; -- only support 3.

• set v_substr_len = length(f_substr);

• select instr(f_str,f_substr) into @p1; -- First real position .

• select instr(substr(f_str,@p1+v_substr_len),f_substr) into @p2; Secondary virtual position.

• select instr(substr(f_str,@p2+ @p1 +2*v_substr_len - 1),f_substr) into @p3; -- Third virtual position.

• if @p1 > 0 && @p2 > 0 && @p3 > 0 then -- Fine.

• select

• concat(substr(f_str,1,@p1 + @p2 + @p3 + (f_times - 1) * v_substr_len - f_times)

• ,f_replace_str,

• substr(f_str,@p1 + @p2 + @p3 + f_times * v_substr_len-2)) into v_result;

• else

• set v_result = f_str; -- Never changed.

• end if;

• -- Purge all session variables.

• set @p1 = null;

• set @p2 = null;

• set @p3 = null;

• return v_result;

• end;

• $$

• DELIMITER ;

• -- 调用函数来更新：

• mysql> update y1 set str1 = func_instr_simple_ytt(str1,'action','dble',3);

• Query OK, 20 rows affected (0.12 sec)

• Rows matched: 20 Changed: 20 Warnings: 0

2. 导出来用sed之类的工具替换掉在导入，步骤如下：（推荐使用）

1）导出表y1的记录。

• mysqlmysql> select * from y1 into outfile '/var/lib/mysql-files/y1.csv';Query OK, 20 rows affected (0.00 sec)




2）用sed替换导出来的数据。

• shellroot@ytt-Aspire-V5-471G:/var/lib/mysql-files# sed -i 's/action/dble/3' y1.csv




3）再次导入处理好的数据，完成。

• mysql

• mysql> truncate y1;

• Query OK, 0 rows affected (0.99 sec)

• mysql> load data infile '/var/lib/mysql-files/y1.csv' into table y1;

• Query OK, 20 rows affected (0.14 sec)

• Records: 20 Deleted: 0 Skipped: 0 Warnings: 0

以上两种还是推荐导出来处理好了再重新导入，性能来的高些，而且还不用自己费劲写函数代码。

那MySQL 8.0 对于以上的场景实现就非常简单了，一个函数就搞定了。

• mysqlmysql> update y1 set str1 = regexp_replace(str1,'action','dble',1,3) ;Query OK, 20 rows affected (0.13 sec)Rows matched: 20 Changed: 20 Warnings: 0




还有一个regexp_instr 也非常有用，特别是这种特指出现第几次的场景。比如定义 SESSION 变量@a。

• mysqlmysql> set @a = 'aa bb cc ee fi lucy 1 1 1 b s 2 3 4 5 2 3 5 561 19 10 10 20 30 10 40';Query OK, 0 rows affected (0.04 sec)




拿到至少两次的数字出现的第二次子串的位置。

• mysqlmysql> select regexp_instr(@a,'[:digit:]{2,}',1,2);+--------------------------------------+| regexp_instr(@a,'[:digit:]{2,}',1,2) |+--------------------------------------+| 50 |+--------------------------------------+1 row in set (0.00 sec)




那我们在看看对多字节字符支持如何。

• mysql

• mysql> set @a = '中国 美国 俄罗斯 日本 中国 北京 上海 深圳 广州 北京 上海 武汉 东莞 北京 青岛 北京';

• Query OK, 0 rows affected (0.00 sec)

• mysql> select regexp_instr(@a,'北京',1,1);

• +-------------------------------+

• | regexp_instr(@a,'北京',1,1) |

• +-------------------------------+

• | 17 |

• +-------------------------------+

• 1 row in set (0.00 sec)

• mysql> select regexp_instr(@a,'北京',1,2);

• +-------------------------------+

• | regexp_instr(@a,'北京',1,2) |

• +-------------------------------+

• | 29 |

• +-------------------------------+

• 1 row in set (0.00 sec)

• mysql> select regexp_instr(@a,'北京',1,3);

• +-------------------------------+

• | regexp_instr(@a,'北京',1,3) |

• +-------------------------------+

• | 41 |

• +-------------------------------+

• 1 row in set (0.00 sec)

那总结下，这里我提到了 MySQL 8.0 的两个最有用的正则匹配函数 regexp_replace 和 regexp_instr。针对以前类似的场景算是有一个完美的解决方案。

⑵ sql 语句 急！！！！ 数据将英文和数字去掉,只保留汉字的sql语句

1、创建测试表，

create table test_replace_str(value varchar2(200));

4、编写语句，将英文和数字去掉,只保留汉字；

select t.*, regexp_replace(value, '[a-zA-Z0-9]', '') sec

from test_replace_str t;

⑶ mysql怎么替换oracle中的regexp_replace正则函数

mysql8.0的写法如下：
REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
8.0以下查找和替换是分开的
SELECT name FROM person_tbl WHERE name REGEXP '^st';

⑷ oracle SQL 正则表达式

正则表达式已经在很多软件中得到广泛的应用，包括*nix（Linux, Unix等），HP等操作系统，PHP，C#，Java等开发环境。
本文主要介绍了关于Oracle中正则表达式的使用方法，下面话不多说了，来一起看看详细的介绍。
Oracle使用正则表达式离不开这4个函数：regexp_like、regexp_substr、regexp_instr、regexp_replace。
1、regexp_like
该函数只能用于条件表达式，和 like 类似，但是使用的正则表达式进行匹配
2、regexp_substr
该函数和 substr 类似，用于拾取合符正则表达式描述的字符子串
3、regexp_instr
该函数和 instr 类似，用于标定符合正则表达式的字符子串的开始位置
4、regexp_replace
该函数和 replace 类似，用于替换符合正则表达式的字符串

⑸ sql 正则表达式 替换字母+数字

我不知道你用的是什么数据库,下面我给你一个Oracle数据库的替换掉(-N+数字)的例子

update[dbo].[Winit库存]set[商品编号]=REGEXP_REPLACE([商品编号],'-Nd+','')

不同的数据库,用的可以用正则表达式的replace函数不一样

SQL Server中我不知道你那里有没有dbo.RegexReplace函数,是不是还要现建这个替换函数

update[dbo].[Winit库存]set[商品编号]=dbo.RegexReplace([商品编号],'-Nd+','',1)

下面是建立RegexReplace函数的sql语句

--如果存在则删除原有函数

IFOBJECT_ID(N'dbo.RegexReplace')ISNOTNULL

DROPFUNCTIONdbo.RegexReplace

GO

--开始创建正则替换函数

CREATEFUNCTIONdbo.RegexReplace

(

@stringVARCHAR(MAX),--被替换的字符串

@patternVARCHAR(255),--替换模板

@replacestrVARCHAR(255),--替换后的字符串

@IgnoreCaseINT=0--0区分大小写1不区分大小写

)

RETURNSVARCHAR(8000)

AS

BEGIN

DECLARE@objRegexINT,@retstrVARCHAR(8000)

--创建对象

EXECsp_OACreate'VBScript.RegExp',@objRegexOUT

--设置属性

EXECsp_OASetProperty@objRegex,'Pattern',@pattern

EXECsp_OASetProperty@objRegex,'IgnoreCase',@IgnoreCase

EXECsp_OASetProperty@objRegex,'Global',1

--执行

EXECsp_OAMethod@objRegex,'Replace',@retstrOUT,@string,@replacestr

--释放

EXECUTEsp_OADestroy@objRegex

RETURN@retstr

END

GO

--保证正常运行的话，需要将OleAutomationProceres选项置为1

EXECsp_configure'showadvancedoptions',1

RECONFIGUREWITHOVERRIDE

EXECsp_configure'OleAutomationProceres',1

RECONFIGUREWITHOVERRIDE

⑹ 正则表达式如何匹配到第二个匹配的满足条件的表达式

按照你的要求写的sql正则表达式如下(两个斜杠中间的部分)替换成美元符号1星号

这里的php的正则表达式可以不用修改的用于sql

下面是一个php语言替换程序的例子,你看看吧.

⑺ mysql sql语句正则表达式

select * from a where b regexp '王'

⑻ sql 正则表达式匹配

• 当我们要进行一些简单的糊涂查询时用百分号(%),通配符(_)就可以了.其中%表达任意长度的字符串,_表示任意的某一个字符.

比如

select*fromempwhereenamelike's%'orenamelike's_';

但如果在一些复杂的查询中关用这两个符号sql语句就会非常复杂,而且也不一定能实现.从Oracle 10g开始引入了在其他程序语言中普通使用的正则表达式.

主要有regexp_like,regexp_replace,regexp_substr,regexp_instr四个正则表达式函数.

• 正则表达式中的元字符:

元字符 意思 例子

说明要匹配的字符是一个特殊字符、常量或者后者引用。（后引用重复上一次的匹配)

匹配换行符
\ 匹配
匹配(匹配 )

^ 匹配字符串的开头位置 ^a匹配arwen.但不匹配barwen.

$ 匹配字符串的末尾位置 en$匹配arwen.但不匹配arwenb.

* 匹配前面的字符0次或多次 a*rwen可以匹配rwen或aaarwen.

+ 匹配前面的字符1次或多次 a+rwen可以匹配arwen或aarwen.但不能匹配rwen.

? 匹配前面的字符0次或1次 a?rwen可以匹配arwen或rwen.但不能匹配aarwen.

{n} 匹配前面的字符恰好是n次，其中n是整数 ar{2}wen可以匹配arrwen.但不能匹配arwen或arrrwen.

{n,m} 匹配前面的字符至少是n次，最多是m次.如果写成{n,} 表示最少匹配n次.没有上限.

ar{1,2}wen可以匹配arwen,arrwen.但不匹配awen或arrrwen.

. 点号,匹配除null,换行以外的任意单个字符 arw.n.可以匹配arwen,arwin.但不能匹配arween或arwn.

(pattern) 括号中pattern是一个子正则表达式，匹配指定pattern模式的一个子表达式。 其实括号就像一般语言表达式中的括号.有时多加些括号可增强可读性.另外的用处见下面关于 的描述.

x|y 匹配“或” x|y可以匹配x或者y

[abc] 可以匹配abc中的任何单个字符 hello[abc]可以匹配helloa,hellob,helloc

[a-z] 可以匹配指定范围内的任何单个字符 hell[a-z]可以匹配hello或者hellz

[::] 指定一个字符类，可以匹配该类中的任何字符 [:alphanum:]可以匹配字符0-9、A-Z、a-z
[:alpha:]可以匹配字符A-Z、a-z
[:blank:]可以匹配空格或tab键
[:digit:]可以匹配数字0-9
[:graph:]可以匹配非空字符
[:lower:]可以匹配小写字母a-z
[:print:]与[:graph:]类似，不同之处在于[:print:]包括空格字符
[:punct:]可以匹配标点符号.,""等等
[:space:]可以匹配所有的空字符
[:upper:]可以匹配大写字母A-Z
[:xdigit:]可以匹配十六进制数字0-9、A-F、a-f

这是对前一次匹配命中的一个后引用，其中n是一个正整数 arw(en)1可以匹配arwenen.注意1前面必须是个加括号的子表达式.

1. regexp_like:

regexp_like(x,pattern[,match_option]),查看x是否与pattern相匹配，该函数还可以提供一个可选的参数match_option字符串说明默认的匹配选项。match_option的取值如下：
‘c’ 说明在进行匹配时区分大小写（缺省值）；
'i' 说明在进行匹配时不区分大小写；
'n'(.)点号能表示所有单个字符,包括换行(俺还不知道什么地方有用到换行.只知道sql里面可以用chr(10)表示换行.
'm' 字符串存在换行的时候当作多行处理.这样$就可匹配每行的结尾.不然的话$只匹配字符串最后的位置.

示例:select * from emp where regexp_like(ename,'^a[a-z]*n$');可以查找ename中以a开头以n结尾的行.例如ename为arwen或arwin或anden.但Arwen不能被匹配.因为默认是区分大小写.如果是select * from emp where regexp_like(ename,'^a[a-z]*n$','i')则可以查找ename为Arwen的行记录.

2. regexp_instr:

REGEXP_INSTR(x,pattern[,start[,occurrence[,return_option[, match_option]]]])用于在x中查找pattern。返回pattern在x中出现的位置。匹配位置从1开始。可以参考字符串函数 INSTR()，参数相关：
'start' 开始查找的位置；
'occurrence' 说明应该返回第几次出现pattern的位置；
'return_option' 说明应该返回什么整数。若该参数为0，则说明要返回的整数是x中的一个字符的位置；若该参数为非0的整数，则说明要返回的整数为x中出现在pattern之后 的字符的位置；
'match_option' 修改默认的匹配设置.与regexp_like里面的相同.

示例:

DECLARE

V_RESULTINTEGER;

BEGIN

SELECT REGEXP_INSTR('hello world','o',1,1,0)INTO V_RESULT

FROM DUAL;

DBMS_OUTPUT.PUT_LINE(V_RESULT);

END;

结果为5.即字母o第一个次出现的位置

如果regexp_instr('hello world','o',1,1,n)其中n为除0之外的整数.比如1,3.则结果为6.表示第一次出现字母o的后面一个字符的位置.

如果regexp_instr('hello world','o',1,2,0)则结果为9.表示第二次出现字母o的位置.

3. regexp_replace:

REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[, match_option]]]])用于在x中查找pattern，并将其替换为replae_string。可以参考字符串函数 REPLACE()，参数同REGEXP_INSTR函数

示例:

DECLARE

V_RESULT varchar2(90);

BEGIN

SELECT REGEXP_REPLACE('hello world','o','x',1,1)INTO V_RESULT

1. FROM DUAL;

DBMS_OUTPUT.PUT_LINE(V_RESULT);

END;

结果为hellx world.

如果REGEXP_REPLACE('hello world','o','x'),则结果为hellx wxrld.

如果 REGEXP_REPLACE('hello w

orld','o','x',1,2)则结果为hello wxrld.

4.regexp_substr:

REGEXP_SUBSTR(x,pattern[,start[,occurrence[, match_option]]])用于在x中查找pattern并返回。可以参考字符串函数 SUBSTR()，参数同REGEXP_INSTR函数.

例如:

DECLARE

V_RESULT VARCHAR2(255);

BEGIN

SELECTREGEXP_SUBSTR('hello world','l{2}')INTO V_RESULT

FROM DUAL;

DBMS_OUTPUT.PUT_LINE(V_RESULT);

END;

结果为ll

查询到匹配的字符串才返回匹配的字符.没查到就返回空.

⑼ sql server中对字段使用正则表达式替换

sql
server中对字段使用正则表达式替换？
.replace(num,
'\d','#')

⑽ 如何在 MySQL 中的正则表达式替换

mysql的正则匹配用regexp，而替换字符串用REPLACE(str,from_str,to_str)

举例如下：

UPDATE myTable SET HTML=REPLACE(HTML,'<br>','') WHERE HTML REGEXP '(<br */*>s*){2,}'

达到的效果：会把所有<br>全部替换掉。

mysql中常用的替换函数

所用到的函数：

locate:

LOCATE(substr,str)

POSITION(substr IN str)

返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在，返回值为 0：

substring

SUBSTR(str,pos,len): 由<str>中的第<pos>位置开始，选出接下去的<len>个字符。

replace

replace(str1, str2, str3): 在字串 str1 中，当str2 出现时，将其以 str3 替代。