I know this may sound like a simple problem, but I have been wrestling with it for a couple of hours and I have not found the solution yet. I am sure somebody must have solved this before, so I am posting this in order not to re-invent the wheel.
How do I delete rows with spaces? I can use the LTRIM, and RTRIM functions to remove the spaces, but then the row contains nothing, not even a NULL.
Here is how I populate the table from another one I loaed with BCP:
INSERT INTO xx SELECT DISTINCT LTRIM(RTRIM( UPPER (SUBSTRING(a,1,30)) ))
ORDER BY 1
CREATE UNIQUE CLUSTERED INDEX idx1 ON xx (a)
Then, I tried:
SELECT * FROM xx WHERE a is NULL
SELECT * FROM xx WHERE a = ""
I found no rows, yet, I know they are there, because I can see them in the output of
SELECT * FROM xx
How do I delete these rows.