Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gmmastros on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

db2 sql extract double rows (different in case sensitive) 1

Status
Not open for further replies.

gdg1976

Programmer
Sep 1, 2021
14
IT
hi all,

how can i extrac from a table only equal rows that are diffent just for case sensitive?

exsample
table1
col1
------
MARK
mark
PAUL
paul
john
ALEX

I would like to select and extract only these rows:
MARK
mark
PAUL
paul

thanks for any idea
bye


 
try this
Code:
select a.COL1                              
from                                       
  TABLE1 a join                            
  TABLE1 b on upper(a.COL1) = upper(b.COL1)
where a.COL1 != b.COL1
Output:
Code:
....+....1....+....2           
COL1                           
MARK                           
mark                           
PAUL                           
paul                           
********  End of data  ********
 
just a doubt:
the result table after the "on condition" is:

MARK MARK
MARK mark
mark MARK
mark mark
PAUL PAUL
PAUL paul
paul PAUL
paul paul
john john
ALEX ALEX

correct or not ?

thanks
 
It is correct. TABLE1 which has one column is joined with itself so the result of the join contains two columns with values which satisfy the join condition. For your desired result you have to select only values from the 1st column that meet the where condition.
 
ok.
I need just this:
how can i extract the opposite? lower case without corresponding upper case

in the exsample

table1
col1
------
MARK
mark
PAUL
paul
john
ALEX

I would like to select and extract only this row:
john

what do you think about this solution ?

select a.COL1
from
TABLE1 a join
TABLE1 b on upper(a.COL1) = upper(b.COL1)
where a.COL1 != upper(a.COL1)
group by (a.COL1)
having count(*) = 1






thanks
 
john is lowercase name and does not have pair with uppercase name (i.e. it is not result from previous select)
Code:
select COL1 from TABLE1
where 
COL1 = lower(COL1) and
COL1 not in (
  select a.COL1                              
  from                                       
    TABLE1 a join                            
    TABLE1 b on upper(a.COL1) = upper(b.COL1)
  where a.COL1 != b.COL1
)
result is
Code:
....+....1....+....2           
COL1                           
john                           
********  End of data  ********
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top