Oracle database is returning string in different to MySql

Question

I have a pattern matching code in Java, which gets the pattern from database When I was doing JUnit with mysql database everything went pretty cool

Original Pattern in database 
[a-zA-Z]{1}[0-9]{2}[a-zA-Z]{3}[0-9]{5}_[0-9]{4}-[0-9]{2}-[0-9]{2}-[0-9]{6}\\.csv
Mysql is retruning as expected and as it's stored.
Oracle is returning with extra backslashes 
[a-zA-Z]{1}[0-9]{2}[a-zA-Z]{3}[0-9]{5}_[0-9]{4}-[0-9]{2}-[0-9]{2}-[0-9]{6}\\\\.csv

Why it's returning differently. Between am using hibernate to fetch the data.

Can we see how you are inserting that pattern in the database? — Pablo
– Pablo, Commented Dec 14, 2012 at 8:43
That's loaded using normal INSERT queries. We don't modify/update the data. We just read the data from database — RaceBase
– RaceBase, Commented Dec 14, 2012 at 9:02

Pablo · Accepted Answer · 2012-12-14 09:39:40Z

1

In MySQL, \ is the escape character for strings, but that's not true in Oracle. That means that this insert will work differently for each of them:

INSERT INTO PATTERNS (ID, VALUE) VALUES (1, '\\\\');

That will insert \\ in MySQL but \\\\ in Oracle. If you want to insert only \\ in Oracle, you have to change the insert to this:

INSERT INTO PATTERNS (ID, VALUE) VALUES (1, '\\');

edited Dec 14, 2012 at 9:39

answered Dec 14, 2012 at 9:16

Pablo

3,6732 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

RaceBase Over a year ago

I am not looking for insertion. When Reading I want to read as it's in database without any escapes or any change to data

Pablo Over a year ago

@NitinGurram What I'm telling you is that I think that your inserts could be wrong, and therefore the patterns saved in oracle and mysql are actually different.

RaceBase Over a year ago

but when i see the data in Toad, both are showing same data in columns :(

RaceBase Over a year ago

I executed same insertion script for both tables

Pablo Over a year ago

@NitinGurram The same insertion script should not produce the same results in Mysql and Oracle, as explained in my answer, but if you see the same data for both in Toad (Oracle doesn't have \\\\ ), then I suppose that I'm wrong and this is not the problem.

|

Collectives™ on Stack Overflow

Oracle database is returning string in different to MySql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related