cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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

Former Member
0 Kudos

Mark,

Many thanks for the detailed insight, observations and suggestions.

After hashing this out in the www.isug.com SIGs, continuing the discussion here, performing a few tests and thinking it over, I opted for the option 1. Cleaning the data before loading it. The logical option.

I kept it simple. I identified the ofending file with vi and replaced the "Control-M" characters with "".

So, I am good now. Thank you very much for your help.


Regards,

Jean-Pierre

P:S. You should have heard me laughing out loud like a madman when I remembered how to fix

        this... My initial approaches where total over kill!

Former Member
0 Kudos

Mark,

Well, I thought I had solved the problem, and almost there, but not quite.

I extracted the data from the table, after removing the "Control-M" characters from the file and there are still a bunch of spaces after the email address in each row in spite of using RTRIM. Here´s the query:

USE test

GO


SELECT LTRIM(RTRIM(a)) FROM xx

GO

You can see that in spite of the LTRIM(RTRIM()) I still have the blanks in there from the Hex Editor I used. See the snapshot of my desktop.

Should I call in SAP and report a bug in in RTRIM? I can go in vi and remove them from there, but gee, RTRIM should have done the job right?

Thank you for your input.

Regards,

Jean-Pierre

former_member188958
Active Contributor
0 Kudos

How are you extracting the data?

If you are using isql, isql is padding the columns with spaces so that they line up because ISQL wants the output to be pretty.

But bcp doesn't care about pretty.

Try this:

CREATE VIEW myview as SELECT LTRIM(RTRIM(a)) FROM xx

Then bcp out from myview.  Do you still see the spaces?

Former Member
0 Kudos

All,

BCPing out the data solved the problem! Thank you Luc .VanderVeurst!

Regards,

Jean-Pierre

PS. Too much marketing and selling makes you forget the basics!

Accepted Solutions (0)

Answers (0)