[ACCEPTED]-Testing for whitespace in SQL Server-whitespace
ltrim(rtrim(isNull(@value,''))) = ''
To compare with NULL, use the IS NULL keyword.
--Generic example: SELECT * FROM MY_TABLE WHERE SOME_FIELD IS NULL; --Instead of SELECT * FROM MY_TABLE WHERE SOME_FIELD = NULL;
if length(@value) = 0 or @value is null 1
should do the trick.
where length(rtrim(ltrim(yourcolumnname))) = 0 1 OR yourcolumnname is null
I just did some testing, and found out something 10 interesting. I used to write my queries 9 like so:
SELECT * FROM TableA WHERE Val IS NOT NULL AND LEN(RTRIM(LTRIM(Val))) > 0
But, you don't actually need to 8 check for null, all you need to do is check 7 the length after trimming the value.
SELECT * FROM TableA WHERE LEN(RTRIM(LTRIM(Val))) > 0
This 6 select weeds out nulls as well as any columns 5 with just white space.
As it turns out, you 4 don't need to trim the value because SQL 3 Server ignores trailing whitespace, so all 2 you actually need it this: SELECT * FROM 1 TableA WHERE LEN(Val) > 0
Rather then performing excessive string 2 manipulation with
RTRIM, just search the 1 expression for the first "non-space".
SELECT * FROM [Table] WHERE COALESCE(PATINDEX('%[^ ]%', [Value]), 0) > 0
You may have fields with multiple spaces 2 (' ') so you'll get better results if 1 you trim that:
where ltrim(yourcolumnname) = ''
More Related questions