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 替代。