Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2005-01-05 : 13:06:21
|
| Any thoughts or experiences for fastest way to check for a non-blank character?WHERE MyChar <> '' -- this is an empty stringWHERE MyChar >= ' ' -- this is a spaceWHERE NOT (MyChar = '')WHERE LEN(MyChar) > 0or all they all the same? |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-01-05 : 13:58:51
|
| Well, I know in VB6, using LEN was the way to go since strings had a length as the first bytes of the string. I'd probably say LEN might be faster in t-sql too, depending on the lenght of MyChar.Or maybe it'sWHERE NOT MyChar IS NULLMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-01-05 : 16:23:28
|
| Can you clarify a little what the requirements are? Specifically:Should NULL pass or fail?Should empty string pass or fail?Should other white spaces characters (e.g., TAB, CR, LF) pass or fail?If you simply want to compare for a single space then I'd go with:WHERE MyChar <> ' ' -- Single spaceThe only caveat is that in SQL Server v6.5 and below, an empty string is equivalent to a single space.Your first example (WHERE MyChar <> '' -- this is an empty string) isn't really comparing for a single space at all.Your second example (WHERE MyChar >= ' ' -- this is a space) does check for a single space but doesn't detect strings that are of lesser collation value. Also, the >= should probably be changed to either = or <>.Third example - See comments for example 1.Your fourth example (WHERE LEN(MyChar) > 0) is misleading since the LEN function in T-Sql has the characteristic that it strips and trailing blanks from the string. Empty string, single space and multiple spaces will all return a LEN of zero.I would try to perform this test on the SQL Server side rather than the Client. In general, you only want to return data to the client that will be used. There are performance issues in network traffic that can slow down the overall throughput.HTH=================================================================Where it is a duty to worship the sun, it is pretty sure to be a crime to examine the laws of heat. -John Morley, statesman and writer (1838-1923) |
|
|
|
|
|
|
|
|
Fastest test ?