SQL Server:选择具有 3 个匹配字符的列 [英] SQL Server : select column with 3 matching characters

问题描述

我正在从 2 个表中选择记录，其中名为 DESC 的第一个表列(前 3 个字符)应与第二个表的项目列匹配.

I am selecting the records from 2 tables in which the 1st table column named DESC (first 3 characters) should match with the project column of the 2nd table.

我想将表 1 列 DESC 中的最后 2 个字符添加到我的输出中，但最后 2 个字符不存在于表 2 列项目中.

I want to get the last 2 characters from Table 1 column DESC to be added in my output, but the last 2 characters are not present in Table 2 column project.

select SUBSTRING(a.[DESC], 1, 3) 
from Table1 a
join Table2 b on SUBSTRING(a.[DESC], 1, 3) = b.project 

输入:第一个表 DESC 列:值:'2AB F YY'

Input: 1st Table DESC Column: Value: '2AB F YY'

第二个表项目列:值:'2AB'

2nd Table Project Column: Value: '2AB'

预期输出:返回值为 2AB 的所有记录

Expected Output: Return all the records of value 2AB

列结果:

'2AB YY'

错误输出:除 2AB 以外的所有值记录

Wrong output: all the records of value starting other then 2AB

推荐答案

一个方案如下

with data
 as (
    select SUBSTRING(a.[DESC],1,3) as first_3_characters,
           ,REVERSE(SUBSTRING(REVERSE(a.[DESC]),1,2)) as last_2_char_tab1  
           ,REVERSE(SUBSTRING(REVERSE(b.project),1,2)) as last_2_char_tab2 characters_tab2  
      from Table1 a
      join Table2 b
        on SUBSTRING(a.[DESC],1,3) = b.project 
     )
select *,CONCAT(first_3_characters,last_2_characters)
  from data
 where last_2_char_tab1 <> last_2_char_tab2  

这篇关于SQL Server:选择具有 3 个匹配字符的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！