Re: Escape character weirdness


David Thielen wrote:


Hi;

This works:
SELECT * FROM demo WHERE (name LIKE 'Da[%]ve')

This dies not:
SELECT * FROM demo WHERE (name LIKE 'Da\%ve') - it returns no rows

Did you try:
SELECT * FROM demo WHERE (name LIKE 'Da\\%ve')


Yet DatabaseMetaData.getSearchStringEscape(); returns "\\".

Any ideas as to what is going on?

Sql Server 2000 and latest JDBC driver.

thanks - dave

Just to be clear, what are you trying to do? Do you have names that really
include the '%' character? You're right. I tried a 'LIKE ' query, trying
to specify the pattern with an escaped '%' to find values with a real '%'
in them, and couldn't....

I get:

Driver version is 1.0.809.102
We inserted 'JOE%JOE'
We inserted 'JOE_JOE'
trying select * from #foo where bar = 'JOE%JOE'
The specific value equals output is: 'JOE%JOE'
trying prepared select * from #foo where bar like 'JOE%JOE'
prepared query like output is: 'JOE%JOE'
prepared query like output is: 'JOE_JOE'
trying prepared select * from #foo where bar like 'JOE\\%JOE'
(finds nothing)
trying prepared select * from #foo where bar like 'JOE[%]JOE'
prepared query bracketed output is: 'JOE%JOE'
(finds the one row)

from this code:

c = d.connect("jdbc:sqlserver://JOE:1433", props );

DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );

Statement stmt = c.createStatement();
stmt.execute("create table #foo(bar varchar(50) primary key)");

PreparedStatement p = c.prepareStatement("insert into #foo values(?)");
p.setString(1, "JOE%JOE");
p.executeUpdate();
p.setString(1, "JOE_JOE");
p.executeUpdate();
p.close();

ResultSet r = stmt.executeQuery("select * from #foo where bar like '%'");
while (r.next()) System.out.println("We inserted '" + r.getString(1) + "'");
r.close();

r = stmt.executeQuery("select * from #foo where bar = 'JOE%JOE'");
System.out.println("trying select * from #foo where bar = 'JOE%JOE'");
while (r.next()) System.out.println("The specific value equals output is: '" + r.getString(1) + "'");
r.close();

p = c.prepareStatement("select * from #foo where bar like ?");
p.setString(1, "JOE%JOE");
r = p.executeQuery();
System.out.println("trying prepared select * from #foo where bar like 'JOE%JOE'");
while (r.next()) System.out.println("prepared query like output is: '" + r.getString(1) + "'");
r.close();
p.setString(1, "JOE\\\\%JOE");
r = p.executeQuery();
System.out.println("trying prepared select * from #foo where bar like 'JOE\\\\%JOE'");
while (r.next()) System.out.println("prepared query escaped output is: '" + r.getString(1) + "'");
r.close();

p.setString(1, "JOE[%]JOE");
System.out.println("trying prepared select * from #foo where bar like 'JOE[%]JOE'");
r = p.executeQuery();
while (r.next()) System.out.println("prepared query bracketed output is: '" + r.getString(1) + "'");
r.close();

.

Loading