While troubleshooting a programming problem today I noticed that you can't use a LIKE search for string columns containing special characters like % or _ without using a special syntax. Čeprav reševanje programsko problem danes sem opazil, da ne morete uporabiti kot so iskanje za godalni stolpce, ki vsebujejo posebnih znakov, kot so% ali _ brez uporabe posebnega sintakso. Figuring out the problem took only a few minutes, but remembering syntax is always easier if you write about it. Poskušal ugotoviti problem je le nekaj minut, toda spomnil sintaksa je vedno lažje, če pišeš o tem.

So yes, this post is solely for my benefit. Tako da, ta post je samo za mojo korist. Hopefully it will help somebody else too. Upam, da bo to pomagalo nekomu drugemu preveč.

Let's say you want to find any fields that contain the text “100%”, so you put together this query: Recimo, da želite najti vseh področjih, ki vsebujejo besedilo "100%", tako da boste skupaj to vprašanje:

SELECT * FROM tablename WHERE fieldname LIKE '%100%%' SELECT * FROM TABLENAME WHERE fieldname PODOBEN "% 100%%"

Instead of what you wanted, you'll get all the rows that contain “100″ as well as the rows that contain “100%”. Namesto tega, kar si hotel, boste dobili vse vrstice, ki vsebujejo "100", kot tudi vrstice, ki vsebujejo "100%".

The problem here is that SQL Server uses the percent sign, underscore, and square brackets as special characters. Problem je, da je SQL Server uporablja znak za odstotek, podčrtaj, in oglatih oklepajih kot posebnih znakov. You simply can't use them as a plain character in a LIKE query without escaping them. Vi preprosto ne morejo jih uporabiti kot navaden znak v PODOBNI poizvedbe, ne da bi jim izogniti.

Square Bracket Escape Square Bracket Escape

You can surround the % or _ with square brackets to tell SQL Server that the character inside is a regular character. Lahko surround% ali _ z oglatimi oklepaji povedati, SQL Server, da je znak znotraj rednega značaj.

SELECT * FROM tablename WHERE fieldname LIKE '%100[%]%' SELECT * FROM TABLENAME WHERE fieldname PODOBEN "% 100 [%]%'

T-SQL ESCAPE Syntax T-SQL ESCAPE Syntax

Alternatively, you can append the ESCAPE operator onto your query, and add a \ character before the value you want to escape. Lahko pa priložiti ESCAPE operaterja na vaše vprašanje, in dodamo \ značaj, preden vrednost, ki jo želite pobegniti.

SELECT * FROM tablename WHERE fieldname LIKE '%100\%%' ESCAPE '\' SELECT * FROM TABLENAME WHERE fieldname PODOBEN "100% \%% 'ESCAPE' \ '

The ESCAPE '\' part of the query tells the SQL engine to interpret the character after the \ as a literal character instead of as a wildcard. ESCAPE "\" del pove poizvedbe SQL motor za razlago znak za \ kot dobesedni znak namesto kot nadomestni znak.

Personally I find the second method easier to deal with, and you can use it to escape a square bracket as well. Osebno se mi zdi drugi način lažje obravnavati, in jih lahko uporabite za pobeg kvadrat, kot tudi nosilec.