Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1293
  • Last Modified:

ignore trailing blanks

I want my database to ignore trailing blanks in character variables.
Select ... where something = 'ABC'
should have the same result as
Select ... where something = 'ABC    '

I found the blank_trimming parameter which was set to false, but setting it true has no change.

Oracle 7.3.3 is running
0
tstrob
Asked:
tstrob
  • 10
  • 4
  • 2
  • +7
1 Solution
 
sgantaCommented:
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 !

0
 
tstrobAuthor Commented:
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...
0
 
tstrobAuthor Commented:
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...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
yorenCommented:
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


0
 
yorenCommented:
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.
0
 
tstrobAuthor Commented:
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    '


0
 
yorenCommented:
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.
0
 
tstrobAuthor Commented:
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 ?



0
 
pviaeneCommented:
    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.

 

     
0
 
tstrobAuthor Commented:
pviaene,

I don´t know how many blanks are added before...
0
 
jcasteelCommented:
Is the table only needed for SELECT statement or are there also inserts/updates/deletes

Greetings

Jan Casteels
0
 
tstrobAuthor Commented:
SELECTs only !
0
 
tstrobAuthor Commented:
Adjusted points to 100
0
 
vlad_impalaCommented:
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.
0
 
jcasteelCommented:
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
0
 
watyCommented:
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.
0
 
petevdbCommented:
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.
0
 
tstrobAuthor Commented:
The relevant column is a varchar2(10), but for Oracle 'BLA   ' is not the same as 'BLA'
0
 
yorenCommented:
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    '.
0
 
jannesCommented:
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

0
 
Mujeeb082598Commented:
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 :)
0
 
jannesCommented:
Can you please give any comment to the rejected answer ..
0
 
tstrobAuthor Commented:
jannes,
your suggestion was already made above

0
 
Mujeeb082598Commented:
Hi :)

Did u try my suggestion making a view with the different name and then referencing it with the private synonym under user for the orignal table. This way u do not have to modify or touch your orignal table and u could test that it works for u or not.

Remeber to rpad the involved coloumn with the field length. Try it might work and i am sure that if the application getting the field length or assuming the same length of your varchar2 field then it will work for sure.
0
 
tstrobAuthor Commented:
thanks, it works now !
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 4
  • 2
  • +7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now