Solved

ignore trailing blanks

Posted on 1998-08-21
25
1,279 Views
Last Modified: 2008-02-01
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
Comment
Question by:tstrob
  • 10
  • 4
  • 2
  • +7
25 Comments
 
LVL 4

Expert Comment

by:sganta
ID: 1081201
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
 
LVL 1

Author Comment

by:tstrob
ID: 1081202
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
 
LVL 1

Author Comment

by:tstrob
ID: 1081203
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
 
LVL 7

Expert Comment

by:yoren
ID: 1081204
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
 
LVL 7

Expert Comment

by:yoren
ID: 1081205
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
 
LVL 1

Author Comment

by:tstrob
ID: 1081206
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
 
LVL 7

Expert Comment

by:yoren
ID: 1081207
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
 
LVL 1

Author Comment

by:tstrob
ID: 1081208
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
 
LVL 1

Expert Comment

by:pviaene
ID: 1081209
    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
 
LVL 1

Author Comment

by:tstrob
ID: 1081210
pviaene,

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

Expert Comment

by:jcasteel
ID: 1081211
Is the table only needed for SELECT statement or are there also inserts/updates/deletes

Greetings

Jan Casteels
0
 
LVL 1

Author Comment

by:tstrob
ID: 1081212
SELECTs only !
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:tstrob
ID: 1081213
Adjusted points to 100
0
 
LVL 3

Expert Comment

by:vlad_impala
ID: 1081214
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
 
LVL 1

Expert Comment

by:jcasteel
ID: 1081215
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
 
LVL 14

Expert Comment

by:waty
ID: 1081216
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
 
LVL 1

Expert Comment

by:petevdb
ID: 1081217
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
 
LVL 1

Author Comment

by:tstrob
ID: 1081218
The relevant column is a varchar2(10), but for Oracle 'BLA   ' is not the same as 'BLA'
0
 
LVL 7

Expert Comment

by:yoren
ID: 1081219
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
 

Expert Comment

by:jannes
ID: 1081220
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
 
LVL 5

Expert Comment

by:Mujeeb082598
ID: 1081221
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
 

Expert Comment

by:jannes
ID: 1081222
Can you please give any comment to the rejected answer ..
0
 
LVL 1

Author Comment

by:tstrob
ID: 1081223
jannes,
your suggestion was already made above

0
 
LVL 5

Accepted Solution

by:
Mujeeb082598 earned 100 total points
ID: 1081224
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
 
LVL 1

Author Comment

by:tstrob
ID: 1081225
thanks, it works now !
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now