Home » SQL & PL/SQL » SQL & PL/SQL » Comparing two strings (Oracle ver 9i)
Comparing two strings [message #277642] Wed, 31 October 2007 01:15 Go to next message
babuanna
Messages: 28
Registered: October 2007
Junior Member
I need to compare every character of two Strings.

Say example;

One value coming from the file "5845789"
and
Another value in the database "5845789 X5"

The validation should be equal and should not create new configuration


Examples:
(a)
Value from the file: 5845789
Value from the database: 5845789 X5
Result of validation: equal, do not create new configuration

(b)
Value from the file: 5845789
Value from the database: 58457892 X5
Result of validation: NOT equal, create a new config

(c)
Value from the file: 5845789
Value from the database: 5845783 X5
Result of validation: NOT equal, create a new config

(d)
Value from the file: 5845789
Value from the database: 5845789X5
Result of validation: NOT equal, create a new config

My Resolution to this issue:

Declare
db varchar2(12) := '5845789 X5';
file varchar2(12) := '5845789';
db1 varchar2(12);
Begin
if db = file then
dbms_output.put_line('The value is equal, keep existing config');
else
select substr(db,1,(INSTRB(db, ' ', 1, 1)-1)) into db1 from dual;
if db1 = file then
dbms_output.put_line('The value is again equal, keep existing config');
else
dbms_output.put_line('The value is not equal');
end if;
end if;
end;


But in a scenario like this the above code fails:

(e)
Value from the file: 5845 789
Value from the database: 5845 789 X5
Result of validation: equal, do not create new configuration

Hence I need something that compares every character from the file to every character in the database.
How do I do it?

Pl help!! urgent....


Thanks and Regards,


Re: Comparing two strings [message #277646 is a reply to message #277642] Wed, 31 October 2007 01:19 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Which of the above have you complied with?

>Pl help!! urgent....
Please explain why is is URGENT for me to solve this problem for you?
Re: Comparing two strings [message #277654 is a reply to message #277642] Wed, 31 October 2007 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As far I understand you check if the "length(value from file)" first characters in database are equal to the value from file.
Is it this?

Regards
Michel
Re: Comparing two strings [message #277662 is a reply to message #277642] Wed, 31 October 2007 01:50 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I think I can come up with a solution, but I'm afraid that I'm going to be banned from this forum if I put it here without pointing out that there is something called netiquette Wink

On the other hand: the solution is - imho - so simple that you can figure it out yourself Wink
Re: Comparing two strings [message #277686 is a reply to message #277662] Wed, 31 October 2007 03:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The most elegant solution I can think of would be to append a space to the file value and then check to see if it was in the database value

Something like:
l_file_value := rtrim(l_file_value)||' ';
IF l_file_value =  substr(l_db_value,1,length(l_file_value)) THEN
   we have a match
END IF;
Re: Comparing two strings [message #277818 is a reply to message #277642] Wed, 31 October 2007 14:44 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Try the following

if file = substr(db,1,instr(db,' ',-1) -1) then
Re: Comparing two strings [message #278090 is a reply to message #277642] Thu, 01 November 2007 15:18 Go to previous message
babuanna
Messages: 28
Registered: October 2007
Junior Member
Appreciate your replies.

The answers given by both JRowBottom and Bill B works.

Thanks very much.
Previous Topic: Row not found
Next Topic: Transactions timing out without errors!
Goto Forum:
  


Current Time: Tue Dec 06 16:32:33 CST 2016

Total time taken to generate the page: 0.06062 seconds