> Mam databazu Sybase SQL s 29.000 pacientami, > potreboval by som vybrat zaznamy s rodnymi cislami > rovankymi na 2 znaky na roznych poziciach, ak mozno a zaroven > s rovnakym menom. > Na 1 znak mam, ale na 2 si netrufam. > nechcel si nieco taketo? SELECT a.meno, a.rc FROM rc AS a INNER JOIN rc AS b ON a.id <> b.id AND a.meno = b.meno WHERE ( STRCMP(SUBSTRING(a.rc FROM 1 FOR 1), SUBSTRING(b.rc FROM 1 FOR 1)) = 0 AND ( STRCMP(SUBSTRING(a.rc FROM 2 FOR 1), SUBSTRING(b.rc FROM 2 FOR 1)) = 0 OR STRCMP(SUBSTRING(a.rc FROM 3 FOR 1), SUBSTRING(b.rc FROM 3 FOR 1)) = 0 OR STRCMP(SUBSTRING(a.rc FROM 4 FOR 1), SUBSTRING(b.rc FROM 4 FOR 1)) = 0 OR STRCMP(SUBSTRING(a.rc FROM 5 FOR 1), SUBSTRING(b.rc FROM 5 FOR 1)) = 0 OR STRCMP(SUBSTRING(a.rc FROM 6 FOR 1), SUBSTRING(b.rc FROM 6 FOR 1)) = 0) ) OR ( STRCMP(SUBSTRING(a.rc FROM 2 FOR 1), SUBSTRING(b.rc FROM 2 FOR 1)) = 0 AND ( STRCMP(SUBSTRING(a.rc FROM 3 FOR 1), SUBSTRING(b.rc FROM 3 FOR 1)) = 0 OR STRCMP(SUBSTRING(a.rc FROM 4 FOR 1), SUBSTRING(b.rc FROM 4 FOR 1)) = 0 OR STRCMP(SUBSTRING(a.rc FROM 5 FOR 1), SUBSTRING(b.rc FROM 5 FOR 1)) = 0 OR STRCMP(SUBSTRING(a.rc FROM 6 FOR 1), SUBSTRING(b.rc FROM 6 FOR 1)) = 0) ) OR ( STRCMP(SUBSTRING(a.rc FROM 3 FOR 1), SUBSTRING(b.rc FROM 3 FOR 1)) = 0 AND ( STRCMP(SUBSTRING(a.rc FROM 4 FOR 1), SUBSTRING(b.rc FROM 4 FOR 1)) = 0 OR STRCMP(SUBSTRING(a.rc FROM 5 FOR 1), SUBSTRING(b.rc FROM 5 FOR 1)) = 0 OR STRCMP(SUBSTRING(a.rc FROM 6 FOR 1), SUBSTRING(b.rc FROM 6 FOR 1)) = 0) ) OR ( STRCMP(SUBSTRING(a.rc FROM 4 FOR 1), SUBSTRING(b.rc FROM 4 FOR 1)) = 0 AND ( STRCMP(SUBSTRING(a.rc FROM 5 FOR 1), SUBSTRING(b.rc FROM 5 FOR 1)) = 0 OR STRCMP(SUBSTRING(a.rc FROM 6 FOR 1), SUBSTRING(b.rc FROM 6 FOR 1)) = 0) ) OR ( STRCMP(SUBSTRING(a.rc FROM 5 FOR 1), SUBSTRING(b.rc FROM 5 FOR 1)) = 0 AND ( STRCMP(SUBSTRING(a.rc FROM 6 FOR 1), SUBSTRING(b.rc FROM 6 FOR 1)) = 0) ) malo by to selectnut vsetky zaznamy, ktore vyhovuju podmienke - rovnost znakov na dvoch miestach (v prvych siestich znakoch) pri zhodnych menach som presvedceny, ze to ide napisat aj krajsim query, ale zatial som nan neprisiel ;-), tak posielam aspon toto nie som si isty ci su tie funkcie podla ansi normy, ale snad budu fungovat aj v sybase, ja som to skusal na mysql peter
This archive was generated by hypermail 2.1.2 : 14. 08. 2002, 14:48 CEST