Home » SQL & PL/SQL » SQL & PL/SQL » String comparison (ORACLE 9i)
String comparison [message #354606] Mon, 20 October 2008 09:55 Go to next message
jd2p
Messages: 3
Registered: October 2008
Location: Tegucigalpa, Honduras
Junior Member
Hi,

At our company, we are adapting our ERP that traditionally ran on SQl Server to run against Oracle 9i. We've encountered a problem with string comparison because SQL Server ignores spaces to the right whereas Oracle doesnt. Is there a configuration setting to control this behavior? Altering every query to add a TRIM() function doesnt seem like an option considering the size of our application.

Example>
* the '_' means blank space

In SQL Server:
'03-000001' equals '03-000001_____'

In Oracle:
'03-000001' does NOT equal '03-000001_____'


I appreciate your responses and suggestions.

JuanDiego
Re: String comparison [message #354613 is a reply to message #354606] Mon, 20 October 2008 10:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Instead of changing the queries, I would fix the data:

UPDATE table_name
SET column_name = TRIM (column_name);
Re: String comparison [message #354614 is a reply to message #354606] Mon, 20 October 2008 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
* the '_' means blank space

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

SQL Server is wrong: something is not equal to nothing.
Anyway, you have to use "trim" or "substr".

Regards
Michel
Re: String comparison [message #354617 is a reply to message #354613] Mon, 20 October 2008 10:28 Go to previous messageGo to next message
jd2p
Messages: 3
Registered: October 2008
Location: Tegucigalpa, Honduras
Junior Member
Thanks Barbara, that DOES solve the problem but I'd like to see if I can find if theres another way since we are talking about over 300 tables with the same issue (I guess the spaces where added by the migration tool). If I dont, then that's the road we'll go.

Regards
Re: String comparison [message #354620 is a reply to message #354614] Mon, 20 October 2008 10:34 Go to previous messageGo to next message
jd2p
Messages: 3
Registered: October 2008
Location: Tegucigalpa, Honduras
Junior Member
Thank you Michel, I just read the "How to format your post?" entry, I needed that.

Using TRIM() is my other option. So just tu sum up, there is no way Oracle will ignore the white spaces to the right?
Re: String comparison [message #354629 is a reply to message #354620] Mon, 20 October 2008 10:45 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That's right. Smile

Regards
Michel
Previous Topic: Faster deletion
Next Topic: List Partion Index and MV expected refresh datetime
Goto Forum:
  


Current Time: Wed Dec 07 10:44:31 CST 2016

Total time taken to generate the page: 0.11156 seconds