bekijk de video van deze week op YouTube

lang geleden heb ik een toepassing gebouwd die gebruikersinvoer heeft vastgelegd. Een kenmerk van de applicatie was om de input van de gebruiker te vergelijken met een database van waarden.

De app voerde deze tekstvergelijking uit als onderdeel van een SQL Server-opgeslagen procedure, waardoor ik de zakelijke logica in de toekomst gemakkelijk kan updaten indien nodig.

op een dag ontving ik een e-mail van een gebruiker waarin stond dat de waarde die ze intypten overeenkwam met een databasewaarde waarvan ze wisten dat die niet overeen zou moeten komen., Dat is de dag dat ik ontdekte SQL Server ‘ s teller intuïtieve gelijkheid vergelijking bij het omgaan met trailing space karakters.

Padded white space

u weet waarschijnlijk dat het Char-gegevenstype de waarde met spaties opvult totdat de gedefinieerde lengte is bereikt:

De functie LEN() toont het aantal tekens in onze tekenreeks, terwijl de functie DATALENGTH() het aantal bytes toont dat door die tekenreeks wordt gebruikt.

In dit geval is de DATALENGTH gelijk aan 10., Dit resultaat is te wijten aan de opgevulde ruimten die zich voordoen na het teken “a” om de gedefinieerde tekenlengte van 10 te vullen. We kunnen dit bevestigen door de waarde om te zetten in hexadecimaal. We zien de waarde 61 (“a “in hex) gevolgd door negen” 20 ” waarden (spaties).

als we het gegevenstype van onze variabele veranderen in VARCHAR, zullen we zien dat de waarde niet langer opgevuld is met spaties:

aangezien een van deze gegevenstypen waarden pads met spatie tekens terwijl de andere niet, wat gebeurt er als we de twee vergelijken?,

in dit geval beschouwt SQL Server beide waarden als gelijk, hoewel we kunnen bevestigen dat de Datalengtes verschillend zijn.

Dit gedrag komt echter niet alleen voor bij gemengde vergelijkingen van gegevenstypen. Als we twee waarden van hetzelfde gegevenstype vergelijken, met één waarde die meerdere spatietekens bevat, ervaren we iets…onverwacht:

hoewel onze twee variabelen verschillende waarden hebben (een blanco in vergelijking met vier spatietekens), beschouwt SQL Server deze waarden als gelijk.,

als we een teken met een aantal achterliggende witruimte toevoegen, zien we hetzelfde gedrag:

beide waarden zijn duidelijk verschillend, maar SQL Server beschouwt ze als gelijk aan elkaar. Het omschakelen van ons gelijkteken naar een soortgelijke operator verandert iets:

hoewel ik zou denken dat een gelijkteken zonder jokertekens zich net als een gelijkteken zou gedragen, voert SQL Server deze vergelijkingen niet op dezelfde manier uit.,

als we terugschakelen naar onze vergelijking met gelijke tekens en onze karakterwaarde met spaties voorvoegen, zullen we ook een ander resultaat zien:

SQL Server beschouwt twee waarden gelijk, ongeacht de spaties aan het einde van een tekenreeks. Spaties die aan een tekenreeks voorafgaan worden echter niet langer beschouwd als een overeenkomst.

Wat is er aan de hand?

ANSI

hoewel de teller intuïtief is, is de functionaliteit van SQL Server gerechtvaardigd., SQL Server volgt de ANSI-specificatie voor het vergelijken van tekenreeksen en voegt witruimte toe aan tekenreeksen zodat ze dezelfde lengte hebben voordat ze worden vergeleken. Dit verklaart de verschijnselen die we zien.

Het doet dit echter niet met de soortgelijke operator, wat het verschil in gedrag verklaart.

vergelijkingen wanneer extra spaties ertoe doen

laten we zeggen dat we een vergelijking willen maken waarbij het verschil in achterliggende spaties ertoe doet.

een optie is om de soortgelijke operator te gebruiken zoals we een paar voorbeelden terug zagen., Dit is echter niet het typische gebruik van een dergelijke operator, dus zorg ervoor dat u commentaar geeft en uitlegt wat uw zoekopdracht probeert te doen door het te gebruiken. Het laatste wat je wilt is een toekomstige onderhouder van je code om het terug te schakelen naar een gelijkteken omdat ze geen jokertekens zien.

een andere optie die ik heb gezien is het uitvoeren van een datalength vergelijking naast de waarde vergelijking:

deze oplossing is echter niet geschikt voor elk scenario., Om te beginnen, je hebt geen manier om te weten of SQL Server Uw waarde vergelijking of datalength predicaat eerst zal uitvoeren. Dit kan ravage op index gebruik wrak en leiden tot slechte prestaties.

een ernstiger probleem kan optreden als u velden met verschillende gegevenstypen vergelijkt., Bijvoorbeeld, bij het vergelijken van een VARCHAR met NVARCHAR data type, is het vrij eenvoudig om een scenario te creëren waarin je vergelijkingsquery met behulp van DATALENGTH een false positive zal veroorzaken:

hier slaat de NVARCHAR 2 bytes op voor elk teken, waardoor de Datalengtes van een enkel teken NVARCHAR gelijk zijn aan een teken + een spatie VARCHAR waarde.

het beste wat u kunt doen in deze scenario ‘ s is uw gegevens begrijpen en een oplossing kiezen die werkt voor uw specifieke situatie.,

en misschien trimmen uw gegevens voor het invoegen (als het zinvol is om dit te doen)!