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

[gdg1976]

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

 

[mikrom]

try this

select a.COL1                              
from                                       
  TABLE1 a join                            
  TABLE1 b on upper(a.COL1) = upper(b.COL1)
where a.COL1 != b.COL1

Output:

....+....1....+....2           
COL1                           
MARK                           
mark                           
PAUL                           
paul                           
********  End of data  ********

 

[gdg1976]

absolutly right!! thanks so much!!

 

[gdg1976]

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

 

[mikrom]

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.

 

[gdg1976]

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

 

[mikrom]

john is lowercase name and does not have pair with uppercase name (i.e. it is not result from previous select)

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

....+....1....+....2           
COL1                           
john                           
********  End of data  ********