ignore trailing blanks

Hello !

By the Grace of God, I have the beautiful solution for your problem.

Let us assume that your datatype width of the name is 30,

Do the following query

SELECT ............................
FROM   ............................
WHERE RPAD(name,30,' ') = RPAD('ABC',30,' ');

This is tested and it works fine.
I hope this helps you.

Good Luck !

Sorry, this does not help me, as I cannot change the select-statements.
I know, that the database can do what I want, but I don´t know how...

Sorry, this does not help me, as I cannot change the select-statements.
I know, that the database can do what I want, but I don´t know how...

tstrob,

BLANK_TRIMMING doesn't actually trim any values unless you're trying to insert a string that's too big for the column. In that case Oracle will trim blanks off  the string so that it fits in the column.

If you can't change the select statements, then you have to change your data. If you can change your insert statements, then you can just replace 'string' with 'RTRIM(string)' so that you never get those blanks into the database in the first place.

If you can't change the inserts either, then you can use a BEFORE INSERT trigger to do the same thing.

You'll also have to write a script to change your existing data. Something like this:

update mytable
set mystring = RTRIM(mystring);

Yuval

Another thought....

Using a view might also work. Rename your table, then create a view with the name the table had. The view does the "select RTRIM(mystring)" before it gets to your application.

Sorry, but this doesn´t work for me too.
My database is ok (no blanks in the strings)

But the software that accesses the database is a little bit strange.

A special function in this software dds some blanks in the whereclause:
SELECT * where value = 'ABCD    '

That sounds like a bug in your database access software, then. I think your best bet is to get the software fixed. Otherwise, even if you come up with some kludge, you're bound to run into problems later.

Of course the software is the problem, but this bug only appears within a particular operation.

Is there definitively no way to make  'ABC   '  match 'ABC' in the database ?

    Tstrob ,does the function add blanks for the whole width of field.

     If this is the case, perhaps using a view might also work after all.
 
    Like yoren said "Rename your table, then create a view with
     the name the table had" .
     
     But instead of using RTRIM use RPAD to add the missing blanks.

 

     

pviaene,

I don´t know how many blanks are added before...

Is the table only needed for SELECT statement or are there also inserts/updates/deletes

Greetings

Jan Casteels

SELECTs only !

Adjusted points to 100

Maybe the original software was written for the CHAR data type rather than the VARCHAR2 datatype, in which case it would expect all the strings to be the same length. If this was the case and you knew the original length of the field then you could use a view to RPAD (i.e add trailing spaces upto the legnth you require) the column as suggested above.

However using the view will adversely affect other code which accesses the data that does not expect 'ABC  ' instead of 'ABC'.

Vlad.

Is there any part of the SQL-statement that can be altered?
Which part of the where statements contains the spaces, is it the database part or is it the literal part?

Greetz

Jan

to have the 2 select the same result
"Select ... where something = 'ABC'"
"Select ... where something = 'ABC    '"

you should use a select like this
Select ... where something like 'ABC*'

But you can't change the select. just a suggestion like that.

I believe vlad_impala is correct.
The table is probably created with column of the CHAR type in
stead of the VARCHAR2 datatype. You should ask your database
administrator to alter this.
(ALTER TABLE MODIFY (columnname VARCHAR2(length));

Oracle will consider two VARCHAR2-strings : 'ABC ' to 'ABC' as equal but not if they are of type CHAR.

Peter.

The relevant column is a varchar2(10), but for Oracle 'BLA   ' is not the same as 'BLA'

tstrob,

Check your queries to find the pattern of blanks that are added (are they a set number of blanks, is it padding to make the string a certain length, etc), and make a view to make your query happy. Forget the char/varchar2 thing. 'BLA' is never equal to 'BLA    '.

Here the final answer :

Change the relevant column into a CHAR(10) :

  DNAME VARCHAR2;
  LOC   CHAR(10);

SQL> select * from dept where loc = 'DALLAS';

   DEPTNO DNAME          LOC
--------- -------------- -------------
       20 RESEARCH       DALLAS

SQL> select * from dept where loc = 'DALLAS ';

   DEPTNO DNAME          LOC
--------- -------------- -------------
       20 RESEARCH       DALLAS


SQL> select * from dept where dname = 'ACCOUNTING';

   DEPTNO DNAME          LOC
--------- -------------- -------------
       10 ACCOUNTING     NEW YORK

SQL> select * from dept where dname = 'ACCOUNTING ';

no rows selected   !!!!!!!!!!

So there is no difference between looking with and without trailing spaces when you use a CHAR.

Met vriendelijke groet,
Jannes Stevens

Hi :)

Instead of renaming the table and creating view with the orignal table name. U can do one thing that u do create a view and than create a private synonym under the user which is accessing that table with the same table name on the view. So following are the steps

create or replace view myview as
select field1, rpad(field2,xyz), ...
from orignal_table
/
xyz <- is the field length of the column.

then u connect as system if u can otherwise ask your dba to create a private synonym under the user or ask dba to give create synonym privilege to the user.

create synonym orignal_table for schemaowner.myview;

Hope this help or give some ideas to continue :)

Can you please give any comment to the rejected answer ..

jannes,
your suggestion was already made above

thanks, it works now !