CODEWITHSUNDEEP
sql-serversql-server-2008-r2
MAK
MAK

Reputation: 7260

Find matching numbers with prefix and suffix

I have the following table with sample records.

CREATE TABLE Testtbl
(
    Number1 int,
    Number2 int,
    AddressA varchar(50),
    AddressB varchar(50),
    originalNumber1 int,
    originalNumber2 int
);

Records:

INSERT INTO Testtbl VALUES(12345,9876,'A1','B1',9876,12345),
                          (4321,761,'A2','B2',924321,761110),
                          (159,9544,'A3','B3',929544,110159),
                          (158,98714,'A4','B4',110158,9298714),
                          (105,9804,'A5','B5',105110,9804110);

I want to find the matching of columnns Number1,originalNumber1 and Number2,originalNumber2. If it's match then it show the Number1 values else originalNumber1.

The columns originalNumber1 and originalNumber2 contains prefix and suffix as shown in the records 92 and 110.

Expected Output:

Num1    Num2
-------------
9876    12345
4321    761
929544  1110159
158     98714
105     9804

My try:

SELECT  CASE WHEN Number1 LIKE '%'+ CAST(OriginalNumber1 AS VARCHAR(20)) +'%' 
                THEN Number1 ELSE OriginalNumber1 END AS Num1,
        CASE WHEN Number2 LIKE '%'+ CAST(OriginalNumber2 AS VARCHAR(20)) +'%' 
                THEN Number2 ELSE OriginalNumber2 END AS Num2
FROM Testtbl

Upvotes: 1

Views: 220

Answers (2)

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

You can do it like this i think 

Select 
case when number1 = originalNumber1Clean  then number1 else originalNumber1  end as num1
,case when number2 = originalNumber2Clean  then number2 else originalNumber2  end as num2 

 from (
select *
,case when left(originalNumber1,2) = 92  
          then substring(cast(originalNumber1 as varchar),3,len(cast(originalNumber1 as varchar)))

 when right(originalNumber1,2) = 92     
then reverse(substring(cast(reverse(originalnumber1) as varchar),3, len(cast(originalNumber1 as varchar)))) 

 when left(originalNumber1,3) = 110  
          then substring(cast(originalNumber1 as varchar),4,len(cast(originalNumber1 as varchar)))

 when right(originalNumber1,3) = 110    
then reverse(substring(cast(reverse(originalnumber1) as varchar),4, len(cast(originalNumber1 as varchar)))) 
           else originalnumber1 end as OriginalNumber1Clean


           ,case when left(originalNumber2,2) = 92  
          then substring(cast(originalNumber2 as varchar),3,len(cast(originalNumber2 as varchar)))

 when right(originalNumber2,2) = 92     
then reverse(substring(cast(reverse(originalNumber2) as varchar),3, len(cast(originalNumber2 as varchar)))) 

 when left(originalNumber2,3) = 110  
          then substring(cast(originalNumber2 as varchar),4,len(cast(originalNumber2 as varchar)))

 when right(originalNumber2,3) = 110    
then reverse(substring(cast(reverse(originalNumber2) as varchar),4, len(cast(originalNumber2 as varchar)))) 
           else originalNumber2 end as OriginalNumber2Clean

         from Testtbl
         )x

Upvotes: 0

Ajay Gupta
Ajay Gupta

Reputation: 1845

Using CASE

SELECT  CASE WHEN OriginalNumber1 LIKE '%'+ CAST(Number1 AS VARCHAR(20)) +'%' 
                   THEN Number1                 
                ELSE OriginalNumber1 END AS Num1,
        CASE WHEN  OriginalNumber2 LIKE '%'+ CAST(Number2 AS VARCHAR(20)) +'%' 
                  THEN Number2              
                ELSE OriginalNumber2 END AS Num2
FROM #Testtbl

Upvotes: 1

Related Questions