Det er ikke de helt vildt streng funktioner, der findes i T-SQL (okay, måske bliver jeg klogere, når jeg får sat mig mere ind i T-SQL udvidelserne i SQL Server 2005, men i hvert fald ikke i SQL Server 2000). Men selv med de simple funktioner kan man lave nogle små fikse ting, hvis det bliver sat rigtigt sammen. I det følgende skal vi finde ud af, hvor mange gange et bestemt ord findes i en kolonne.

Vi har følgende table:

CREATE TABLE tbl_Demo (
    DemoID INT,
    Description VARCHAR(8000)
)    

I den tabel vil vi finde ud af hvor mange gange order "server" i hver rækkes Description felt. Det kan gøres på følgende måde:

Vi tager kort fortalt længden af Description feltet inkl. alle gange order "server" måtte optræde, og trækker længden af Description feltet uden ordet "server" fra. For at finde antallet af gang ’server’ indgår dividerer vi dette tal med længden af "server".

Ved også at bruge udtrykket i en Order By kan vores statement tilmed bruges som en søgefunktion, der sortere de mest relevante resultater øverst:

Måske ikke lige frem optimalt hvad angår performance, men trods alt en prioriteret søgning.

Default collation på SQL Server er case-insensitive. Det er som oftest også det smarteste, så man ikke behøver koncentrere sig om at skrive f.eks. object navne med den rigtige case. Ind imellem kunne det dog være rart, hvis man kunne lave en case-sensitive WHERE-clause, f.eks. i forbindelse med, at en bruger angiver password til login.

Eks:

SELECT * FROM tbl_User WHERE Password = 'superfly'

Denne returnerer både dem der har password: superfly, Superfly, SUPERFLY og lign., hvilket ikke er hensigtsmæssigt.
 
Følgende lille trick giver til gengæld det ønskede resultat:

SELECT * FROM tbl_User WHERE CONVERT(BINARY, Password) = CONVERT(BINARY, 'superfly')

Nu vil kun brugere med password superfly blive returneret. Den binære værdi for f.eks. s og S er forskellig, så nu risikerer vi ikke at få uønskede rækker returneret.

Den betænksomme SQL programmør vil nu gøre opmærksom på, at et evt. index på kolonnen Password ikke længere vil kunne anvendes, men at der vil skulle en table-scan til for at finde den ønskede brugere.

Dette kan man komme ud over med denne færdig SELECT:

SELECT * FORM tbl_User WHERE Password = 'superfly' AND CONVERT(BINARY, Password) = CONVERT(BINARY, 'superfly')

Et index kan nu anvendes til at finde de brugere, der har password superfly i en elle anden form, og til sidst fjernes de brugere, der så ikke har det på den rigtige form (den rigtige case).

Vores mål er nu nået og vi har lavet en case-sensitive WHERE-clause på en database med case-insensitive collation.

Som nævnt tidligere er denne blog også lidt at betragte som min digitale hukommelse. Efterhånden er jeg ved at have fundet disse SQL statements så mange gange i mine andre arkiver, at de er ved at hænge ved. Men måske der er andre, der ikke har så meget orden i papirarkiverne som jeg(!?!), og som finde det lettere at slå op på nettet. Her kommer 3 metoder til at slette dubletter i en tabel:

Vi vil gå ud fra en tabel med personer, som har følgende definition:

CREATE TABLE tbl_Person (
Id INT IDENTITY(1, 1),
FirstName VARCHAR(50),
LastName VARCHAR(50)
)

og målet er at slette dubletter af kombinationen FirstName og LastName.

Metode 1:
DELETE tbl_Person
FROM tbl_Person AS P1
WHERE EXISTS (SELECT 1 FROM tbl_Person AS P2 WHERE P2.Id > P1.Id and P1.FirstName = P2.FirstName and P1.LastName = P2.LastName)

Skeler man til performance er denne metode noget af det mest effektive man kan komme op med.

Metode 2:
DELETE P1
FROM tbl_Person AS P1
INNER JOIN tbl_Person AS P2 ON P1.FirstName = P2.FirstName AND P1.LastName = P2.LastName AND P1.Id > P2.Id

Og endelig metode 3:
DELETE tbl_Person
FROM tbl_Person AS P1
WHERE Id > (SELECT MIN(Id)
FROM tbl_Person AS P2
WHERE P1.FirstName = P2.FirstName
AND P1.LastName = P2.LastName)

Alle tre metoder gør det samme, nemlig sletter dubletter. Som altid når man sletter flere rækker er det en god idé at SELECT’e rækkerne ud før man sletter dem, så man sikrer at det ser rigtigt ud.