使用交叉联接创建并使用STUFF编辑的列中的唯一值

Question

我试图从一个表中提取一列，并从不需要的东西和从另一个表匹配的一些单词中清除它。我得到这个代码

create table dbo.abbEx(
name nvarchar(50),
full_name nvarchar(50)
)
INSERT INTO abbEx
(name, full_name)
VALUES
('DT','Delete This'),
('NN','Not Needed'),
('CM','Cut Me');
create table dbo.wordsToEdit(
names nvarchar(50),
)
INSERT INTO wordsToEdit
(names)
VALUES
('Delete This "THIS MUST NOT BE DELETED" Delete This'),
('DT HELLO WORLD'),
('SAVE THIS WORDS N"N'),
('THIS ONE WASNT EDITED'),
('THIS ONE -CM WASNT EDITED TOO'),
('Cut     Me EDITED ONE');
select distinct RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(wte.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))) as orig_name, edited_name FROM wordsToEdit wte
cross join (select RTRIM(LTRIM(ISNULL(STUFF(ISNULL(STUFF(RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))),
PATINDEX(a.name+'%', RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
len(a.name),
''),RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
PATINDEX('%'+a.name, ISNULL(STUFF(RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))),
PATINDEX(a.name+'%', RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
len(a.name),
''),RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))))),len(a.name),
''), STUFF(RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))),
PATINDEX(a.name+'%', RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
len(a.name)+1,
'')))) as edited_name
from (select name from abbEx union select full_name from abbEx) a
cross join (select names FROM wordsToEdit) b) c

然后我需要对edited_name列做更多的工作，但我得到的是：

+---------------------------------------------------------------+-----------------------------------+
|   orig_name                                                   |   edited_name                     |
+---------------------------------------------------------------------------------------------------+
|   cut me edited one                                           |   <null>                          |
|   cut me edited one                                           |   edited one                      |
|   cut me edited one                                           |   hello world                     |
|   cut me edited one                                           |   save this words                 |
|   cut me edited one                                           |   this must not be deleted        |
|   delete this this must not be deleted delete this            |   <null>                          |
|   delete this this must not be deleted delete this            |   edited one                      |
|   delete this this must not be deleted delete this            |   hello world                     |
|   delete this this must not be deleted delete this            |   save this words                 |
|   delete this this must not be deleted delete this            |   this must not be deleted        |
|   dt hello world                                              |   <null>                          |
|   dt hello world                                              |   edited one                      |
|   dt hello world                                              |   hello world                     |
|   dt hello world                                              |   save this words                 |
|   dt hello world                                              |   this must not be deleted        |
|   save this words nn                                          |   <null>                          |
|   save this words nn                                          |   edited one                      |
|   save this words nn                                          |   hello world                     |
|   save this words nn                                          |   save this words                 |
|   save this words nn                                          |   this must not be deleted        |
|   this one cm wasnt edited too                                |   <null>                          |
|   this one cm wasnt edited too                                |   edited one                      |
|   this one cm wasnt edited too                                |   hello world                     |
|   this one cm wasnt edited too                                |   save this words                 |
|   this one cm wasnt edited too                                |   this must not be deleted        |
|   this one wasnt edited                                       |   <null>                          |
|   this one wasnt edited                                       |   edited one                      |
|   this one wasnt edited                                       |   hello world                     |
|   this one wasnt edited                                       |   save this words                 |
|   this one wasnt edited                                       |   this must not be deleted        |
+---------------------------------------------------------------+-----------------------------------+

我需要的是：

+---------------------------------------------------------------+-----------------------------------+
|   orig_name                                                   |   edited_name                     |
+---------------------------------------------------------------------------------------------------+
|   cut me edited one                                           |   edited one                      |
|   delete this this must not be deleted delete this            |   this must not be deleted        |
|   dt hello world                                              |   hello world                     |
|   save this words nn                                          |   save this words                 |
|   this one cm wasnt edited too                                |   this one cm wasnt edited too    |
|   this one wasnt edited                                       |   this one wasnt edited           |
+---------------------------------------------------------------+-----------------------------------+

如果我将第一个交叉联接中的所有select放入ISNULL，并返回orig_name。如果STUF的结果为null，则edited_name列中将包含orig_name中所有未编辑的字符串。

我需要的是，如果字符串是用abbEx编辑的，请将编辑后的值添加到edited_name列中，如果不是，请将orig_name添加到edited name中，而不是null，如您所见：

|   this one cm wasnt edited too                                |   this one cm wasnt edited too    |
|   this one wasnt edited                                       |   this one wasnt edited           |
+---------------------------------------------------------------+-----------------------------------+

所有这些值都必须是唯一的
我该怎么做？

第一个交叉联接操作肯定有问题，但我不知道具体是什么，以及如何用任何其他联接替换它。

Sooo。。。我做了一些更正：

select distinct RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(wte.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))) as orig_name, edited_name FROM wordsToEdit wte
left join (select RTRIM(LTRIM(ISNULL(STUFF(ISNULL(STUFF(RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))),
PATINDEX(a.name+'%', RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
len(a.name),
''),RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
PATINDEX('%'+a.name, ISNULL(STUFF(RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))),
PATINDEX(a.name+'%', RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
len(a.name),
''),RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))))),len(a.name),
''), STUFF(RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))),
PATINDEX(a.name+'%', RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
len(a.name)+1,
'')))) as edited_name
from (select name from abbEx union select full_name from abbEx) a
cross join (select names FROM wordsToEdit) b) c on wte.names like '%'+edited_name+'%'

现在我得到了这个：

+---------------------------------------------------------------+-----------------------------------+
|   orig_name                                                   |   edited_name                     |
+---------------------------------------------------------------------------------------------------+
|   cut me edited one                                           |   edited one                      |
|   delete this this must not be deleted delete this            |   this must not be deleted        |
|   dt hello world                                              |   hello world                     |
|   save this words nn                                          |   save this words                 |
|   this one cm wasnt edited too                                |   <null>                          |
|   this one wasnt edited                                       |   <null>                          |
+---------------------------------------------------------------+-----------------------------------+

但是，在edited_name为null的情况下，如何将值从orig_name放入edited_name？

Answer 1

完成

select distinct RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(wte.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))) as orig_name, ISNULL(edited_name, RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(wte.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))) as edited_name FROM wordsToEdit wte
left join (select RTRIM(LTRIM(ISNULL(STUFF(ISNULL(STUFF(RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))),
PATINDEX(a.name+'%', RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
len(a.name),
''),RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
PATINDEX('%'+a.name, ISNULL(STUFF(RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))),
PATINDEX(a.name+'%', RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
len(a.name),
''),RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))))),len(a.name),
''), STUFF(RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' '))),
PATINDEX(a.name+'%', RTRIM(LTRIM(replace(replace(replace(replace(replace(lower(b.names), '-', ' '), '"', ''),' ','<>'),'><',''),'<>',' ')))),
len(a.name)+1,
'')))) as edited_name
from (select name from abbEx union select full_name from abbEx) a
cross join (select names FROM wordsToEdit) b) c on wte.names like '%'+edited_name+'%'