如何在 SQL 中使用正則運算式從這種特定模式的字串中僅提取電子郵件？
我有什么： tb_1
| 日志訊息 |
|---|
| Alan Robert <[email protected]> 被分配到 <[email protected]> 和 <[email protected]> |
| Alan Robert <[email protected]> 未分配給 <[email protected]> 和 <[email protected]> |
我想要什么： tb_2
| 電子郵件_1 | email_2 | 電子郵件_3 |
|---|---|---|
| [email protected] | 理查德@yahoo.com | [email protected] |
| [email protected] | [email protected] | [email protected] |
我已經有一個解決方案，但是 tb_1 表有大量的行，所以我的查詢輸出需要太多時間。這就是為什么我認為正則運算式可能會更節省時間。
我的查詢：
with cte as(
Select replace(replace(replace(replace(right(@logmessage, len(logmessage)-charindex('<', logmessage) 1),
Case when logmessage like '%unassigned%' Then ' was unassigned to '
When logmessage like '%assigned%' then ' was assigned to ' End , '.'),' and ', '.'),
'<', '[' ),'>', ']') logmessage
From tb_1)
Select
PARSENAME(logmessage, 3) AS email_3,
PARSENAME(logmessage, 3) AS email_2,
PARSENAME(logmessage, 1) AS email_1
From cte
uj5u.com熱心網友回復：
使用輔助函式
示例或dbFiddle
Declare @YourTable Table (LogID int,[Logmessage] varchar(500)) Insert Into @YourTable Values
(1,'Alan Robert <[email protected]> was assigned to <[email protected]> and <[email protected]>')
,(2,'Alan Robert <[email protected]> was unassigned to <[email protected]> and <[email protected]>')
Select A.LogID
,B.*
From @YourTable A
Cross Apply [dbo].[tvf-Str-Extract-JSON](LogMessage,'<','>') B
結果
LogID RetSeq RetVal
1 1 alan.robert@gmail.com
1 2 richard@yahoo.com
1 3 nelson@gmail.com
2 1 alan.robert@gmail.com
2 2 khanjoyty@gmail.com
2 3 katy@gmail.com
然后，調整結果將是一件小事
有興趣的TVF
CREATE FUNCTION [dbo].[tvf-Str-Extract-JSON] (@String varchar(max),@Delim1 varchar(100),@Delim2 varchar(100))
Returns Table
As
Return (
Select RetSeq = row_number() over (order by RetSeq)
,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
From (
Select RetSeq = [Key] 1
,RetVal = trim(Value)
From OpenJSON( '["' replace(string_escape(@String,'json'),@Delim1,'","') '"]' )
) C1
Where charindex(@Delim2,RetVal)>1
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/477200.html
上一篇：沒有回圈的T-SQL迭代計算