Query doesn't find existing data | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query doesn’t find existing data

SQL Server 2000 Enterprise While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others. For example: SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899; would not find the data sought. However, if I put in: SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John#%92; the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can#%92t figure out a reason. The front-end application displays the data without any apparent problems. Ideas please.
Is the [ID Number] column an int or varchar? If it’s a varchar look for spaces in the data like ‘ 2358899’ or ‘2358899 ‘ or any other strange hidden characters or carrige returns.
If [ID Number] is not Int, then you may have spaces in the field(s). Try the following query to fix your data. It removes leading or trailing spaces. UPDATE tblPersonalInformation SET [ID Number] = LTRIM(RTRIM([ID Number])) You may use it for all columns that you think you may have problem with them. Like: UPDATE tblPersonalInformation
SET [ID Number] = LTRIM(RTRIM([ID Number])),
Name = LTRIM(RTRIM(Name))
CanadaDBA
[ID Number] is nvarchar datatype, so I ran the query using trims as suggested and it worked like a charm. Thanks.
]]>