July 12, 2012 at 1:52 pm
Hi,
I have been working with SQL for a while but do not consider myself an expert but can hold my own. I found an interesting syntax difference that I can only attribute to database setting or server. This issues happens on SQL 2008 R2 but not SQL 2008. I only have one server of each version available so I am not sure if it is across all installations of that paparticular version.
2008 Works
Select * from <table> where <varchar(10)> like '110'
2008 R2 No Work
Select * from <table> where <varchar(10)> like '110'
2008 R2 Work
Select * from <table> where <varchar(10)> like '11%' (returns too much)
Select * from <table> where LTRIM(RTRIM(<varchar(10)>)) like '110' (same results as 2008)
I know what your thinking...invisible charicters or white space but I checked. What I did was to add LEN(<varchar(10)>) to the select clause and the output is always 3 and if I trim the whole column, no change in results. Very baffling...I bet this is so obvious I am gonna look like a fool...Any ideas?
July 12, 2012 at 1:59 pm
LIKE without a wildcard is just an equality test.
I'm using SQL 2008 R2, and this works just fine:
SET ANSI_PADDING OFF;
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T;
CREATE TABLE #T (
C1 VARCHAR(10));
INSERT INTO #T (C1)
VALUES ('110 '),('110'),('111');
SELECT *
FROM #T
WHERE C1 LIKE '110';
I get both rows in the final query.
Most likely, you have different data in the different databases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 12, 2012 at 2:05 pm
Most comparitors will implicitly pad strings before comparing them, but LIKE will only do this if the whitespace is on the right.
Try this and see if it works:
Select * from <table> where '110' LIKE <varchar(10)>
If it does, then I'd bet your problem is trailing whitespace.
Len() will not include whitespace on the right end of a string. To be sure you are getting the length including whitespace, you can use DataLength() to get the data size of the value, reverse the string before using Len(), or append a character to the end before using Len() and subtracting 1 from the result.
July 12, 2012 at 3:30 pm
There are reasons why I need like, but this is the simplest case that reproduces the behavior. I have tried exactly what you suggest and found the same issue. I am not convinced there is not some kind of extra character in the data. Tomorrow I am going to try to migrate the data to a different database and try again. This should rule out the server/database question and point directly at the data...
July 12, 2012 at 3:37 pm
I was not aware of the padding on the right with LIKE and LEN, Good to know. I will try the alternate query (hopefully it will work with my actual query). Good idea using DataLength. I will try all of these tomorrow. I have a working query now but with 6 trims is is ugly...always looking for the elegant solution. Thanks and I will update tomorrow.
July 13, 2012 at 6:24 am
You nailed it sestell1! The reverse trick and appending the character showed I had 10 characters. Now if they are white space or invisible characters still remain to be determined. The data source is from Excel imports so there are probably some tabs, new line, return characters present...
However since I have limited access to the import process, I will have to live with the ugly code. On the bright side I learned a little about LIKE, LEN and inherent right side TRIM/Padding. Both responses helped me solve this and prove it is data related. Thank you, I love this site!
🙂
July 14, 2012 at 8:17 am
Check the ANSI_PADDING settings on the database, the column in question and the connection between the 2 different systems.
I am betting you will find a difference between the 2 which will explain the different behavior.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2012 at 9:56 am
Thanks for the ANSI_Padding idea and will do that but I think I found the cause late yesterday afternoon. I am forced to use linked server object and when I checked with the DBA I found the column data type was not VarChar as Everyone told me but nchar instead...once I found this out, it all made sense...also explains why DataLength always returned 10 after the LtrimRtrim of the column data. So, moral of the story, trust nothing until you see it with your own eyes...I taught several people a thing or two with this one, I knew there was a simple explanation to this issue. Thanks to all for their help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply