Skip to Content
0
Former Member
Aug 06, 2013 at 10:26 PM

SAP Sybase ASE - How to remove rows with spaces from a table?

892 Views

Greetings,

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)) ))

FROM x

ORDER BY 1

GO
CREATE UNIQUE CLUSTERED INDEX idx1 ON xx (a)

GO

Then, I tried:

SELECT * FROM xx WHERE a is NULL

or

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.

Regards,

Jean-Pierre

Attachments

test.log.zip (11.3 kB)