SQL:用替换词更新表中的行 [英] SQL : Update row in table with replace word

问题描述

我有这样的桌子:-

ID               Name                 Email                 UserName
1               Johen mak         [e-mail]
2               jameel mak        [e-mail]
3               gawil gorgy       [e-mail]
4               mak jor           [e-mail]

现在我需要插入 Email 列中的 UserName 值，但没有 @yahoo.com 或 @gmail.com ....

Now i need to insert into UserName value from Email column but without @yahoo.com or @gmail.com ....

但是如果有重复的UserName.必须添加 _1 或 _2 ...

But if there duplicit UserName. It must be add _1 or _2 ...

但是如果任何用户有英文UserName，我们就不能改变，只有当UserName不是英文时才改变和更新.所以表格必须像:-

But if Any user have English UserName,we must not change, Only change and update if UserName not English. So The table must be like :-

ID               Name                 Email                 UserName
1               Johen mak         [e-mail]              jojo
2               jameel mak        [e-mail]              jojo_1
3               gawil gorgy       [e-mail]             jojo_2
4               mak jor           [e-mail]               gop

我该怎么做！！！

推荐答案

这是 SQL-SERVER 语法，EnglishNames 只是一个带有名字的表格，你必须从某个地方填写它.

This is SQL-SERVER syntax, EnglishNames is just a table with names, you have to fill it from somewhere.

DECLARE @users TABLE 
( 
    id INT, 
    name VARCHAR(32),
    email VARCHAR(32),
    username VARCHAR(32)
)
INSERT INTO @users
select ROW_NUMBER() OVER(ORDER BY Name) AS Id, Name, Email, substring(Email, 0, charindex('@', Email)) AS UserName
from Users

update @users set username = 
case 
    when (select count(*) from EnglishNames e where e.Name = username) > 0 then username + '_' + CAST(id AS VARCHAR(10))
    when (select count(*) from EnglishNames e where e.Name = username) = 0 then username
    end
where
id not in (
select min(id) from @users
group by username
)

select * from @users

这篇关于SQL:用替换词更新表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！