Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Eric den Doop
  Where is Eric den Doop?
 Houten
 Netherlands
 Eric den Doop
 To: sqlpro
  Where is sqlpro?
 
 New Zealand
 sqlpro
 Tags
Subject: RE: this must be interesting query
Thread ID: 50887 Message ID: 50927 # Views: 1 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Tuesday, September 07, 2004 8:51:49 AM         
   


> Hi Friends
>
> This must be good one.I have duplicate data like
>
> 123x
> 123X
> 1234
> 1234
>
> how can i write query which returns records that r same but different case.in the abv example i need 123x
>
> I tried
> select fld1 from mytable group by fld1 having count(fld1)>1
> but this returns even 1234 record also.
> any ideas please.
> Thanks
> cheers

not fully tested, but I think this should get you in the right direction.
CREATE CURSOR test (cfield C(4))
INSERT INTO test VALUES ("123x")
INSERT INTO test VALUES ("123X")
INSERT INTO test VALUES ("1234")
INSERT INTO test VALUES ("1234")
INSERT INTO test VALUES ("1234")
INSERT INTO test VALUES ("12A4")
INSERT INTO test VALUES ("12a4")
INSERT INTO test VALUES ("12a4")
INSERT INTO test VALUES ("1bA3")
INSERT INTO test VALUES ("1Ba3")
INSERT INTO test VALUES ("1BA3")
SELECT UPPER(cfield) AS u_cfield, count(*) as ncount ;
FROM test ;
WHERE NOT LEN(TRANSFORM(VAL(cfield))) = LEN(cfield) ;
GROUP BY u_cfield ;
HAVING ncount > 1

--
Eric den Doop
www.foxite.com - The Home Of The Visual FoxPro Experts

ENTIRE THREAD

this must be interesting query Posted by Rajani @ 9/7/2004 12:46:45 AM
RE: this must be interesting query Posted by Eric den Doop @ 9/7/2004 8:51:49 AM
cheers Posted by Rajani @ 9/15/2004 4:03:44 AM
RE: this must be interesting query Posted by Rajani @ 9/15/2004 4:16:08 AM
RE: this must be interesting query Posted by Joey Bancud @ 9/9/2004 7:43:58 AM
RE: this must be interesting query Posted by Rajani @ 9/15/2004 4:07:24 AM