sql - 模式匹配在 SQL 中，字符出现在任何地方但按顺序排列

Question

我正在尝试编写一个查询来修复无效的电子邮件格式。我收到的无效电子邮件格式为first@name_company@com.

字符始终按'@' '_' '@'该顺序出现。

如果这是格式，那么我将替换@为.和_以@获得正确的格式：

[e-mail]

我正在考虑这样的情况：

When @email like '[a-z,0-9,_,-,@]_[a-z,0-9,_,-@]'  THEN replace(replace(replace(@email,'@','.'),'_','@'))

如果我将“_”作为这样的通配符，这似乎才有效'[a-z,0-9,_,-,@]%_%[a-z,0-9,_,-@]'

标签： sqlsql-serverpattern-matching

Answer 1

我使用您的具体示例找到了两种方法来做到这一点。

create table #myTemp
(someText varchar(50))

insert into #myTemp
values
('first@name_company@com')

select 
    -- check based on length after replacing the characters with nothing
    case when len(someText) - len(replace(replace(someText,'@',''),'_','')) = 3 
    then replace(replace(someText,'@','.'),'_','@')
    end as LengthCheck,
    -- check the position of @ to the left and right of _
    case when charindex('@',(Right(someText, CHARINDEX('_', reverse(someText))-1))) > 0
        and charindex('@',(LEFT(someText, CHARINDEX('_', someText)))) > 0
    then replace(replace(someText,'@','.'),'_','@')
    end as CharCheck
from #myTemp