Urmăriți videoclipul din această săptămână pe YouTube

cu mult timp în urmă am construit o aplicație care a capturat intrarea utilizatorului. O caracteristică a aplicației a fost de a compara intrarea utilizatorului cu o bază de date de valori. aplicația a efectuat această comparație de text ca parte a unei proceduri stocate SQL Server, permițându-mi să actualizez cu ușurință logica de afaceri în viitor, dacă este necesar.

într-o zi, am primit un e-mail de la un utilizator care spunea că valoarea pe care o tastau se potrivește cu o valoare a bazei de date despre care știau că nu ar trebui să se potrivească., Aceasta este ziua în care am descoperit comparația contra egalității intuitive a SQL Server atunci când se ocupă de caracterele spațiului final.

Căptușit spațiu alb

Vă sunt, probabil, conștienți de faptul că CHAR tip de date tampoane valoarea cu spații până definite lungimea este de ajuns:

funcția LEN() afișează numărul de caractere din șir noastre, în timp ce DATALENGTH funcția() ne arată numărul de octeți folosit de acel șir. în acest caz, DATELUNGIMEA este egală cu 10., Acest rezultat se datorează spațiilor căptușite care apar după caracterul ” a ” pentru a umple lungimea CHAR definită de 10. Putem confirma acest lucru prin conversia valorii în hexazecimal. Vedem valoarea 61 („a „În hex) urmată de nouă valori” 20 ” (spații).

Dacă ne schimbăm variabile date de tip VARCHAR, vom vedea valoarea nu mai este căptușit cu spații:

Având în vedere că unul dintre aceste tipuri de date tampoane de valori cu caractere spațiu în timp ce celălalt nu, ce se întâmplă dacă vom compara cele două?,

În acest caz SQL Server consideră că ambele valori egale, chiar dacă putem confirma că DATALENGTHs sunt diferite.

acest comportament nu apare numai cu comparații mixte de tip de date. Dacă comparăm două valori de același tip de date, cu o valoare care conține mai multe caractere spațiale, experimentăm ceva…neașteptat:

Chiar dacă două variabile au valori diferite (un gol în comparație cu patru caractere spațiu), SQL Server consideră aceste valori egale.,

dacă adăugăm un caracter cu unele spații albe, vom vedea același comportament:

ambele valori sunt clar diferite, dar SQL Server le consideră egale între ele. Trecerea noastră semn egal la un ASTFEL de operator schimbă puțin lucrurile:

Chiar dacă aș crede că o PLACE, fără nici o metacaractere s-ar comporta ca un semn egal, SQL Server nu face aceste comparații la fel.,

Dacă vom comuta înapoi la semnul egal comparație și prefix caracterul nostru valoarea cu spatii vom observa, de asemenea, un rezultat diferit:

SQL Server consideră două valori egale, indiferent de spații care apar la sfârșitul unui șir. Spațiile care preced un șir cu toate acestea, nu mai este considerat un meci.

ce se întâmplă?

ANSI

în timp ce contra intuitiv, funcționalitatea SQL Server este justificată., SQL Server urmează specificația ANSI pentru compararea șirurilor, adăugând spațiu alb șirurilor, astfel încât acestea să aibă aceeași lungime înainte de a le compara. Acest lucru explică fenomenele pe care le vedem. cu toate acestea, nu face acest lucru cu operatorul similar, ceea ce explică diferența de comportament.

comparații când spațiile suplimentare contează

să spunem că vrem să facem o comparație în care diferența de spații finale contează.

o opțiune este să folosiți operatorul LIKE așa cum am văzut câteva exemple înapoi., Cu toate acestea, aceasta nu este utilizarea tipică a operatorului similar, așa că asigurați-vă că comentați și explicați ce încearcă să facă interogarea dvs. folosind-o. Ultimul lucru pe care îl doriți este un viitor responsabil al codului dvs. pentru a-l schimba înapoi la un semn egal, deoarece nu văd niciun caracter wild card.

o Altă opțiune pe care am văzut-este de a efectua o DATALENGTH comparație, în plus față de valoarea de comparație:

Această soluție nu este potrivit pentru fiecare scenariu cu toate acestea., Pentru început, nu aveți cum să știți dacă SQL Server va executa mai întâi comparația valorii sau predicatul DATALENGTH. Acest lucru ar putea distruge utilizarea indicelui și poate provoca performanțe slabe. o problemă mai gravă poate apărea dacă comparați câmpuri cu diferite tipuri de date., De exemplu, atunci când se compară un VARCHAR să NVARCHAR tip de date, este destul de ușor de a crea un scenariu în cazul în care comparația interogare folosind DATALENGTH va declanșa un fals pozitiv:

Aici NVARCHAR magazine 2 octeți pentru fiecare caracter, provocând DATALENGTHs de un singur caracter NVARCHAR să fie egal cu un caracter + un spațiu VARCHAR valoare. cel mai bun lucru de făcut în aceste scenarii este să înțelegeți datele dvs. și să alegeți o soluție care va funcționa pentru situația dvs. particulară.,

și poate tăiați datele înainte de inserare (dacă are sens să faceți acest lucru)!