Help me understand this unusual query... [modified]
-
Hi, There was a small problem with our app where it allowed users to enter white-space only characters in one of the data fields. So now I'm trying to clean this up. A colleague of mine sent me this unusual SQL query to find the whitespaces. The script looks like this:
SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE ASCII(LEFT(tblField1,1)) = 32This query returned 40 rows. Now, when I try either of the following:
SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE tblField1 = ' ' -- with a single whitespaceor
SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE tblField1 = '' -- empty stringThe above 2 queries return 200,000+ rows. Why is this so?
Rafferty
modified on Sunday, December 27, 2009 10:23 PM
-
Hi, There was a small problem with our app where it allowed users to enter white-space only characters in one of the data fields. So now I'm trying to clean this up. A colleague of mine sent me this unusual SQL query to find the whitespaces. The script looks like this:
SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE ASCII(LEFT(tblField1,1)) = 32This query returned 40 rows. Now, when I try either of the following:
SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE tblField1 = ' ' -- with a single whitespaceor
SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE tblField1 = '' -- empty stringThe above 2 queries return 200,000+ rows. Why is this so?
Rafferty
modified on Sunday, December 27, 2009 10:23 PM
Hi, the first query selects all rows where tblField1 holds a string with a first character equal to a space. the other two require the entire tblField1 to equal something that probably gets trimmed down to an empty string, hence returning all rows with that field empty. That obviously is quite a different query. IMO the more elegant query would use
...WHERE tblField1 LIKE ' %'
using a single space and the wildcard character %. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Merry Christmas and a Happy New Year to all.
-
Hi, the first query selects all rows where tblField1 holds a string with a first character equal to a space. the other two require the entire tblField1 to equal something that probably gets trimmed down to an empty string, hence returning all rows with that field empty. That obviously is quite a different query. IMO the more elegant query would use
...WHERE tblField1 LIKE ' %'
using a single space and the wildcard character %. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Merry Christmas and a Happy New Year to all.
So the = operator does not check for exact string equality... I knew before that = is case insensitive but to think that it also trims the string! Is LIKE the more preferable conditional operator then? Is there a performance difference between = and LIKE? Say for example
WHERE tblField1 = 'something'vs.WHERE tblField1 LIKE 'something'Rafferty
-
So the = operator does not check for exact string equality... I knew before that = is case insensitive but to think that it also trims the string! Is LIKE the more preferable conditional operator then? Is there a performance difference between = and LIKE? Say for example
WHERE tblField1 = 'something'vs.WHERE tblField1 LIKE 'something'Rafferty
1. I'm not sure, I think it stripped the spaces, got an empty constant, and returned all records. 2. LIKE makes most sense when it contains a wildcard, and = does not accept wildcards; without wildcards databases can take big advantage of indexing, with wildcards they can't, so most often LIKE is much slower. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Merry Christmas and a Happy New Year to all.
-
1. I'm not sure, I think it stripped the spaces, got an empty constant, and returned all records. 2. LIKE makes most sense when it contains a wildcard, and = does not accept wildcards; without wildcards databases can take big advantage of indexing, with wildcards they can't, so most often LIKE is much slower. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Merry Christmas and a Happy New Year to all.
So using LIKE will not use the database indexing even if there are no wildcards in the passed string?
Rafferty
-
So using LIKE will not use the database indexing even if there are no wildcards in the passed string?
Rafferty
I don't know what goes on inside a specific database, that is up to the guys implementing all of it; it may or may not be documented, and it might depend on circumstances, such as available memory and disk space. The simple rule is: use LIKE if you need a wildcard, and expect it to be slower. If you need more, look for the documentation and perform your own experiments (on sufficiently large data sets). :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Merry Christmas and a Happy New Year to all.
-
I don't know what goes on inside a specific database, that is up to the guys implementing all of it; it may or may not be documented, and it might depend on circumstances, such as available memory and disk space. The simple rule is: use LIKE if you need a wildcard, and expect it to be slower. If you need more, look for the documentation and perform your own experiments (on sufficiently large data sets). :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Merry Christmas and a Happy New Year to all.
hm... maybe I'll do some experimenting when I find the time. Thanks for your help!
Rafferty
-
hm... maybe I'll do some experimenting when I find the time. Thanks for your help!
Rafferty
You're welcome. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Merry Christmas and a Happy New Year to all.
-
So using LIKE will not use the database indexing even if there are no wildcards in the passed string?
Rafferty
Microsoft SQLServer will use indexes (if available) when you use the like statement without wildcards. Even if you do use wildcards, SQL will use indexes if possible. if you compare to a string witch starts with a wildcard, SQL will NOT use indexes.
Wout Louwers
-
Hi, There was a small problem with our app where it allowed users to enter white-space only characters in one of the data fields. So now I'm trying to clean this up. A colleague of mine sent me this unusual SQL query to find the whitespaces. The script looks like this:
SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE ASCII(LEFT(tblField1,1)) = 32This query returned 40 rows. Now, when I try either of the following:
SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE tblField1 = ' ' -- with a single whitespaceor
SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE tblField1 = '' -- empty stringThe above 2 queries return 200,000+ rows. Why is this so?
Rafferty
modified on Sunday, December 27, 2009 10:23 PM
Something is wrong...in ansi sql databases: The rows returned in the first query will be equal to or greater than the second. All of the rows in the second will be included in the first. The rows returned in the third query will never be in the first or second. Need to figure this out before thinking about indexes and wildcards. I would change the second qry as such:
WHERE tblField1 = char(32)just to be sure because-select ascii(' ') --this equals 9
select ascii(' ') --this equals 32Create and put data in your Table1 and test if you are not convinced.
-
Microsoft SQLServer will use indexes (if available) when you use the like statement without wildcards. Even if you do use wildcards, SQL will use indexes if possible. if you compare to a string witch starts with a wildcard, SQL will NOT use indexes.
Wout Louwers
Wow this is very useful! Thanks a lot! :thumbsup:
Rafferty