SQL Server使用in子句从表中选择一个字符串

I'm having a strange SQL Server issue.

我有一个奇怪的SQL Server问题。

Using the following query:

使用以下查询:

SELECT id FROM table WHERE id IN ('id1', 'id2', .......)

when id is nchar(30) and 'id1','id2' are values, I get a result which isn't in the values I entered.

当id是nchar(30)和'id1','id2'是值时,我得到的结果不在我输入的值中。

Is it possible that SQL Server is searching for a string contained in the values?

SQL Server是否可能正在搜索值中包含的字符串?

Added

query:

SELECT Word FROM WordDictionary WHERE Word IN ('DESPERADO', 'WWW.MYSAVINGS.COM', 'RELIED', 'GALS/GUYS....U', 'MISSOULA', 'STARING...WHY', 'OHIO,,,WHAT', 'ALEYO"MEANS', 'EXCRETE', 'POETERS', 'REMOVAL?IF', 'MOTOT', 'VIEW/SOUND', 'SCHOLD', 'FLINGS', '300000', 'BIGBANG', 'INVOKE', 'COMPLIER', 'UPNISHAD', 'FLUFF/LINT', 'DONATED?..PLEASE', 'EPHEDRINE', 'AGAIN-', 'WHUNT', 'LEVE', 'ARIEL', 'SEIZURES,AND', 'ANYON', 'WELL~AS', 'GGGGGGGGOOOOOOOOOOOOOOOOODDDDD', 'ALGERIA', 'LONDON...CAN', 'TWAIN''S', 'BUTIFUL', 'CIRRHIOSIS', 'PHP-NUKE', 'SCREWD', 'RECONNECT', 'BAND...''SIGUR', 'ROS''', 'DEFLEPOARD', 'FIHGT', 'DRE''S', 'ACQUAINTED', '77067', 'INCREASE/DECREASE', 'AWHILE..SHOULD', 'BABY???..MORE', 'CHRISTEN', 'SUNSLIFE', 'HYANCINTHS', 'NOVEMEBER', 'IEEE', 'IRENE', '5"4', 'BAYSIDE', 'DOJO', 'PEOPLES::DO', 'INFORMATION/ANSWER', 'BLACKWORM', 'MYWIFE.D', '42D', 'COLONEL', 'ESCAPES', 'KW', 'WASH/CLENSE', 'ENCOURAGES', 'HOLINESS', '4710', 'MONOATOMIC', 'FORM-', 'NAVIGATIONS', 'ASHLIEY(TWINS', 'ALIAS....WHAT', 'MARIOKART', 'HORNYNESS', 'CONVERSIONS', 'NUIT', 'PARISTEL:0660442290PL06', 'PUSSY', 'WILLOWS', 'BOYFRIEND/BABYDADDY', 'PARASITES', 'TABOILD', 'J.T', 'TERESEA', '---FREE---', 'KAMORA', 'SIMONS', 'FORSYTHIA', 'RAZORTHOUGHT', 'ABSINTHE', '9-3', 'BAIT-CASTING', 'CUMULATIVE', 'HELP>>>', 'MATZO', 'LIMOSINE', 'SCD353', 'BANGARAM', 'BRUNEL', 'KWTV878', 'NEAPOLITAN', 'OFYOUR', '2SIN', '²', '3SINX', 'IMPERFECTION', 'NONBELIEF', 'FLEM', 'NON-ADJACENT', 'WASHINGTION', 'WHERE/IF', 'BRONTE''S', 'WUTHERING', 'SOMEONE/A', 'TEAM.WHAT', 'PRESIDENT,WHEN', 'DIRICHLET', 'X-AND', 'Y-INTERCEPTS', 'STAMPED', 'PROCRDURE', 'AK32', '*67', 'HANUKKAH', 'MONIE', 'TAGAYTAY', 'NATURES', 'HASS', 'TORMENTS', 'PROPOTIONAL', 'SUDERLAND', 'CONROL', 'CONSEQUENCE', 'SAW?YOU', 'WITHDREW', 'PMT', 'JAIL?WAT', 'DEFFEND', '-12>8X', '4X>6', 'MX-C550', '6-DISC', 'SVQ3', 'BULLSHITING', 'PWEAZE', '23SECONDS', 'VISHWANATHAN''S', 'INTERNALIZING', 'MCCAFFERTY''S', 'TODAY...AND', 'CHANCE....WOULD', 'DEC.''41', '''45', 'HAILLE', 'SELASSIE', 'OF...GREENDAY', 'DEAD/ARMY', 'EX-NFL', 'JACKSONVILLE,FL', 'ATLANTA,HOUSTON,OR', 'ECCENTRICITY', 'CONIC', '[e-mail]', '[e-mail]', 'DISCRET', '_______', 'ROMACE', 'SUBCATEGORY', 'REDUDUCE', 'EXERCISER', 'MUNITE', 'MESSENGER.SO', 'NIGEL', 'PLANER', 'QUESTION?31576*66496139', 'KODJOE', '919', '1847', 'D.WADE', 'HUMAN''S', 'MULTI-NATIONAL', 'GOGGLE', 'GAAP', 'CONFUSED.IONT', 'ST8', 'ROOM/HOME', 'BOLB', 'GRANDMA-', 'PARSON', 'BELIZE', 'UNITY', 'AWARDS''', 'TOGHTHER', 'LONDON+GREATER', 'JERSEY...IE', 'NETGAR', 'NBC,ABC', 'CONON', 'RECIDENT', 'CANCERS', 'PITTSBURGH/INDY', 'CREATETH', 'MUSICAL''S', 'HEELLLLPPPP', 'MASRER', 'NAME,AND', 'ANAEROBIC', 'SPACIAL', 'SPOUSE/SIGNIFICANT', 'TRIGNOCEPHALY', 'RAW''S', 'BLOGGIN', '9.2', 'FLATTENING', 'FLOWER,ANIMALAND', 'EXPRESSES', 'FRDS', 'NOT?PLS', 'CLEARIFY', 'CLEARFIELD/JEFFERSON', 'HACE', 'FELICIANO', 'MEDICINE--THEY', 'DASCHUND', 'PLINTER', 'SKETCHY', 'I..WHAT', 'JUVENTINA', 'SOMUCH', 'SHEEN', 'HALEX', '11-IN-1', 'URBANIZATION', 'WILWOOD', 'CALIPER', 'NERVE-RACKING', 'OBSESESSION', 'EZRA', 'TALBOT', 'SHOCKWAVE', 'PASCO', '300$-600$', '108,000', '*BOYZ', 'ONLY*PLZ', 'INTERNET)CAN', 'CONCISE', 'TOP40', 'HICUPPS', '4:00', 'OPOSITE', 'NETWORKER?=', 'Q-LINK', 'HSG', 'AMINE', 'RIGHTS,,HOW', 'EMILIANO', 'PEDREGON', 'DILEMA', 'GROUPTHINK', 'MONTEAL', '17...&', '13:04', 'NASHIK', 'NOBIA', 'LINEWIRE', 'ISOCKS', 'DAY........WAIT', 'KATY', 'BODERLINE', 'CONNORS', 'WHWRE', 'CROMWELL', 'COE', '1+1=', 'UMMM.....WHATS', 'BOND''S', 'VIEWLOADER', 'MAXIS', '[e-mail]', 'SCISSORS', 'UNSANITARY', 'KANSANS', 'SALINA', '''ARENA''', '''CAROLINA''', 'BIOMAGNIFICATION', 'BIOASSIMILATION', 'WOMBATS', 'POOS', 'ARSES', 'SOCIALIZATION', 'GROUPS,RACES', 'FULL-BLOOD', 'TASMANIAN', 'INCLINE', 'PICA', 'FIGGERED', '9.HE', 'RETINOBLASTOMA,IS', 'COAT''S', '2MOWRO', 'DOTHAN', 'DIFRNT', 'DEPICTS', 'WHAK', 'NETHERLAND', 'FORTHE', 'OFMICROSOFT', 'INDIGO', 'I-MAC', 'SHANGHAINESE', 'DINOSAOUR', 'SUBCAMPS', 'CARDINAL', 'NEBRASKA''S', 'KOMO', 'PIRATE/SAILING', 'ZOPICLONE', 'CRYPTIC', 'CLUE"COULD', 'WHEELCHAIR,NO', 'OVERWIEGHT.DOES', 'ALL..MY', 'BOYFRIIEND', 'MAGTECH', 'TROUBLES...ANY', 'BACK,REDUCED', 'JEWELY', 'CRAFTSMEN''S', 'HAUNTER', 'GENGAR', 'CRYSTAL.WHERE', 'LAHORE', 'SANDLER''S', 'ACCENT...WHAT', 'WOOOOOAAAAAAAAH', 'W''T', '[e-mail]', 'SPEICES', 'MCFLY', 'BIOCONVERSION', 'GUERRERO', 'CATHOLICS......WHAT', 'NOV.1--', 'ECONOLINE', 'AMOVIE', 'COUNSELLING', 'HANDSPAN', 'ATTIUTE', 'HAIR??HELP', 'PLASTIC?CERAMIC', 'TITLEIST', 'REISDENTS', '7''S', 'FERMATS', 'JBW&CWW', 'RB''S', 'KENYON', 'BAPTIST''S', 'THUMPERS', 'THOZ', 'HATZ', 'MSNISMS', 'POLL/SURVEY', 'INFUSION', 'FUNDRAISER', 'PROTECTS', 'ANTOINO', 'SYALLBUS', 'GCSES', 'SPIDER,SNAKE,DOG,CAT', 'KNOW.PLEASE', 'CHACH', 'DISSAPIONTED', 'TODGER', 'SH*TTING', 'LODESTONE', 'SARBANES', 'OXLEY', 'ANOTHR', 'RELATIONSHIP????HOW', 'T9', 'JIGGLY', 'GOOD?IS', 'HARDEN', 'DESERT?I', 'SIGNIFANT', 'WEDO', 'SCHAT', 'LQ', 'TENCHI', 'ME...WHAT''S', 'ERUPTED', '£40', '£150', 'UPGRATE', 'I500', '2003SE', 'PROMOT', 'SALUTES', 'GRAEME', 'SOUNESS', 'SERRONE', 'AHAVE', 'BUSTSA', 'IMPARTIAL', 'SUGARCULT', 'RFID', 'SWIPE', '30X', 'HOUSE''S', 'BI-WAY', 'BLYTHE,CA', 'REDDER', 'PLUMPER', 'LEFTWINGERS', 'WHINGE', 'ANNOYING..!!1', 'SENSORY', 'ADHD/ODD', 'ZYBAN', 'RAMP', 'SUB-WOOFER''S', 'TATTOOIST', '477', 'SOFISTCATED', 'B/J', 'ALLURE', 'THIS?(SEE', 'PAKISTANIS:DO', 'PLEASE?I', '*ING', 'KNOW(DOCS', 'WHITESMOKE', 'SCREEN''S', 'SPINK', 'YOUI', 'DELICATE', 'MISDIAGNOSED', 'DIPERNO', 'HIM.PLEASE', '45CM', '35CM', 'ENOUGHT', 'DECIMETERS?PUT', 'RDMB', 'HOMELOANS', 'HAA', 'ORIGIANL', 'RESTON', 'ZINNIAS', 'PERENNIAL', 'WHOOPIE', 'CUSHIONS', 'POOFS', 'CAT,ITS', 'TIME,ITS', 'TUXEDOS', 'CHICKAN', 'WHISLE', 'RUMBLING', '7LEVELS', 'YOUFROM', 'CEMO', 'RECURRENT', 'LARYNGEAL', 'W/NO', 'SOUCRE', 'COMMA', 'WORDCUB', 'WOMEN:HAVE', 'PIGGYBACK', 'ANOLE', 'CHRISTIANSEN', 'SWEEPSTAKES,GETTING', 'WON.HOW', 'EUDORA5.1', 'EXPRESS,NETSCAPE4.X', '6.X', '''COURT', 'WISK''', '4/13', '50-70', 'DAUGHTER,CAN', 'UVULA', '''AYURVEDA''', 'ACUPUNTURE', 'BAJA', 'REASONIBLE', 'PUZZY', 'SORCERER', 'DISEASES/SICKNESS', 'ANUS/RECTUM?CAN', 'HOME?WHAT', 'COCETH', 'KELLOGGS', 'DAISUKE')

result:

2

Added

CREATE TABLE [dbo].[WordDictionary](
[Word] [nchar](30) NOT NULL,
[Count] [int] NOT NULL,
    CONSTRAINT [PK_WordDictionary] PRIMARY KEY CLUSTERED 
    (
      [Word] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

4 个解决方案

#1

Your IN list contains the item '²'.

IN列表包含项目“²”。

I'll be very surprised if that isn't the source of the issue (though it doesn't actually match for me under my default collation)

如果这不是问题的根源我会感到非常惊讶(虽然它在我的默认排序规则下实际上并不匹配)

#2

Try making the strings in the IN() clause Unicode by prefixing with N

尝试使用N前缀在IN()子句Unicode中创建字符串

IN (N'Id1', N'Id2',...)

#3

AFAIK, SQL Server would respond as you and I are both expecting. So I knocked up a little test example. and sure enough, I got only the results expected.

AFAIK,SQL Server会像你和我都期待的那样回应。所以我敲了一个小测试例子。果然,我只得到了预期的结果。

With it being a text column, using LIKE/BETWEEN and other similar clauses can cause problems if you are not careful, but WHERE IN should be fairly clear cut.

如果你是一个文本列,使用LIKE / BETWEEN和其他类似的子句可能会导致问题,如果你不小心,但WHERE IN应该相当明确。

I suggest you post some DDL + DML that includes both the 'good' and the unexpected results. In theory, you are doing the right thing, but until we see what you are working with, we won't be able to spot the 'gotcha'.

我建议你发布一些包含'好'和意外结果的DDL + DML。从理论上讲,你做的是正确的,但在我们看到你正在使用的东西之前,我们将无法发现'陷阱'。

#4

select * from test 
where data IN ('Aa' COLLATE SQL_Latin1_General_CP1_CS_AS, 'Ab' COLLATE SQL_Latin1_General_CP1_CS_AS)