Cannot find a particular record by searching on non-primary key
in [Server Configuration]
|
From: gilak on 11 Jun 2010 04:04 I am a novice in sql. I have a sql2000 running on win2000. I have a table (tblCustomer) with a single primary key (CustomerID) and other fields including Surname and Forename. For one particular record query select * from tblCustomer where Surname='Blogg' and Forename='Joe' does not return any results but query select * from tblCustomer where CustomerID='ABCDEF' return the correct records. The retuned record has surname and forename 100% correct. I am concerned that they may be other records in the database where similar problem may manifest itself. If I update the Joe Blogg record with EXACTLY the same surname and forename the first query return the correct results. Any help on this will be appreciated.
From: John Bell on 11 Jun 2010 06:41 On Fri, 11 Jun 2010 01:04:49 -0700 (PDT), gilak <bkasmai(a)gmail.com> wrote: >I am a novice in sql. I have a sql2000 running on win2000. I have a >table (tblCustomer) with a single primary key (CustomerID) and other >fields including Surname and Forename. For one particular record query > >select * from tblCustomer where Surname='Blogg' and Forename='Joe' >does not return any results but query > >select * from tblCustomer where CustomerID='ABCDEF' return the correct >records. > >The retuned record has surname and forename 100% correct. I am >concerned that they may be other records in the database where similar >problem may manifest itself. If I update the Joe Blogg record with >EXACTLY the same surname and forename the first query return the >correct results. Any help on this will be appreciated. Hi How do you know that the surname and forename is correct. you may want to try: select 'select * from tblCustomer where Surname='''+ surname + ''' and Forename='' + sorename + '''' from tblCustomer where CustomerID='ABCDEF' Then run the output statement. If that doens't work then look at DBCC commands such as DBCC CHECKTABLE or possibly DBCC CHECKDB John
From: obiron via SQLMonster.com on 11 Jun 2010 08:09 Check your collation. Some are case sensitive and I think some are also sensitive to white space at the end of the string. SELECT 'X'+ Surname + 'X' as 'SurnameCheck',* from tblCustomer where UPPER (Surname) like '%BLOGG%' and UPPER(Forename) like '%JOE%' using wildcards (%) kills the query optimiser as it often cannon use an index but you could at least check your data The X prefix and suffix will show you whether there is any whitespace in the string. gilak wrote: >I am a novice in sql. I have a sql2000 running on win2000. I have a >table (tblCustomer) with a single primary key (CustomerID) and other >fields including Surname and Forename. For one particular record query > >select * from tblCustomer where Surname='Blogg' and Forename='Joe' >does not return any results but query > >select * from tblCustomer where CustomerID='ABCDEF' return the correct >records. > >The retuned record has surname and forename 100% correct. I am >concerned that they may be other records in the database where similar >problem may manifest itself. If I update the Joe Blogg record with >EXACTLY the same surname and forename the first query return the >correct results. Any help on this will be appreciated. -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201006/1
From: gilak on 11 Jun 2010 10:23 John & Obirion Thank you for your responses. Query select * from tbCustomer where CustomerID=ABCDE returns the correct record with no apparent white spaces, whereas CustomerID=ABCDE Surname = Blogg Forename = Joe Query select * from tblCustomer where Surname=Blogg and Forename=Joe returns no record . However after executing query: Update tblCustomer set Surname=Blogg , Forename=Joe where CustomerID=ABCDE and changing the name to the same forename and surname and then execution the query below: select * from tblCustomer where Surname=Blogg and Forename=Joe returns the correct record. John: Your suggested query: 'select * from tblCustomer where Surname='''+ surname + ''' and Forename='' + sorename + '''' from tblCustomer where CustomerID='ABCDEF' returns Incorrect syntax near the keyword 'from'. Probably you meant the above to be 2 separate quries? Obirion: your query works but it returns a customer with partially similar surname and forename XBLOGGEX FGHKLM NULL NULL NULL BLOGGE JOES (please note the diffrence in surname=BLOGGE and Forename="JOES") You query apparently suggets no white spaces.
From: John Bell on 11 Jun 2010 18:46
On Fri, 11 Jun 2010 07:23:04 -0700 (PDT), gilak <bkasmai(a)gmail.com> wrote: >John & Obirion > >Thank you for your responses. Query �select * from tbCustomer where >CustomerID=�ABCDE� � >returns the correct record with no apparent white spaces, whereas > >CustomerID=�ABCDE� >Surname = �Blogg� >Forename = �Joe� > >Query �select * from tblCustomer where Surname=�Blogg� and >Forename=�Joe� returns no record . However after executing query: > >Update tblCustomer set Surname=�Blogg� , Forename=�Joe� where >CustomerID=�ABCDE� > >and changing the name to the same forename and surname and then >execution the query below: > >select * from tblCustomer where Surname=�Blogg� and Forename=�Joe�� >returns the correct record. > >John: Your suggested query: > >'select * from tblCustomer where Surname='''+ surname + ''' and >Forename='' + sorename + '''' >from tblCustomer where CustomerID='ABCDEF' > >returns �Incorrect syntax near the keyword 'from'�. Probably you >meant the above to be 2 separate quries? > >Obirion: your query works but it returns a customer with partially >similar surname and forename > >XBLOGGEX FGHKLM NULL NULL NULL BLOGGE JOES (please note the diffrence >in surname=BLOGGE and Forename="JOES") > >You query apparently suggets no white spaces. Looks like you missed out the first SELECT but there are a couple of typos. It is a query that returns a SQL Statement that you can cut and paste into the query window and run. Because you have to double up the single quote to get it to appear in the string you end up with three single quotes at the start/end of a string and 4 quotes for the single quote in a string on it's own (if that makes sense?) SELECT 'select * from tblCustomer where Surname='''+ surname + ''' and Forename=''' + forename + '''' from tblCustomer where CustomerID='ABCDEF' If your names fit a pattern then you could use LIKE and pattern matching, and there are examples in Books Online or see http://msdn.microsoft.com/en-us/library/ms179859(SQL.100).aspx You may want to use SOUNDEX see http://msdn.microsoft.com/en-us/library/ms189282.aspx If this is not sufficient the Full Text Searching should offer more. John |