Compare two columns of same row SQL

Question

I have a table sample that looks like :

KeyID   Name    ID    Location  
--------------------------------------------
20063   A DA    20439     AEP  DA           
20063   A DA    20063     APS DA            
20063   A DA    20032     APS RT          
20063   A RT    20032     APS RT          
20063   B RT    20032     APS DA Legacy

Only select rows where Name and Location both ends with either DA or RT.

Exception: if location ends with legacy , include row regardless.

          20063 A DA    20439     AEP  DA           
          20063 A DA    20063     APS DA    
          20063 A RT    20032     APS RT
          20063 B RT    20032     APS DA Legacy

How do I compare column of same row for this SQL?

Is it always the last 2 letters?

Vatev
– Vatev

2014-04-07 17:16:00 +00:00
Commented Apr 7, 2014 at 17:16 — Vatev
– Vatev, Commented Apr 7, 2014 at 17:16
Yes. Always the last 2 letters.

Cannon
– Cannon

2014-04-07 17:16:34 +00:00
Commented Apr 7, 2014 at 17:16 — Cannon
– Cannon, Commented Apr 7, 2014 at 17:16
Which DBMS are you using? Postgres? Oracle?

user330315
– user330315

2014-04-07 19:51:04 +00:00
Commented Apr 7, 2014 at 19:51 — user330315
– user330315, Commented Apr 7, 2014 at 19:51

DarkSide · Accepted Answer · 2014-04-08 09:05:11Z

2

Something like this should work in MySQL, but most likely in other SQLs too:

SELECT * FROM TABLE
WHERE location like '% Legacy'
    OR (
        SUBSTRING(name,-2)='DA' AND SUBSTRING(location,-2)='DA'
        OR
        SUBSTRING(name,-2)='RT' AND SUBSTRING(location,-2)='RT'
    )

edited Apr 8, 2014 at 9:05

answered Apr 7, 2014 at 17:19

DarkSide

3,7191 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Wesley Gordon · Accepted Answer · 2014-04-07 19:28:47Z

0

if using Microsoft SQL you may have to do something like this. You reverse the string, pull the first 2 characters using substring from the first character with a length of 2, then reverse it again to put it back the right way.

SELECT * FROM TABLE 
WHERE location like '% Legacy'
     OR
     (
            reverse(substring(reverse(name),1,2))='DA' 
            AND 
            reverse(substring(reverse(location),1,2))='DA'
        OR
            reverse(substring(reverse(name),1,2))='RT' 
            AND 
            reverse(substring(reverse(location),1,2))='RT'
     )

answered Apr 7, 2014 at 19:28

John Wesley Gordon

9202 gold badges17 silver badges40 bronze badges

Collectives™ on Stack Overflow

Compare two columns of same row SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related