escaped underscore

[Díaz]

Does Mckoi understand escaped underscores in a LIKE
statement? For example,

SELECT * FROM EMAILS_TABLE WHERE EMAIL LIKE '%\_%'

... so that it returns all email addresses
containing underscores.

I tried the backslash but it doesn't seem to work. I
also tried a varied number of backslashes (2, 4, etc),
to allow for Java backslash escaping, regular
expressions, etc., to no avail.

If true, is there a workaround? I can use prepared
statements, but still need to write the right SQL.
Thanks in advance

Gonzalo Díaz

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com


---------------------------------------------------------------
Mckoi SQL Database mailing list http://www.mckoi.com/database/
To unsubscribe, send a message to mckoidb-***@mckoi.com

[Mark Salter]

Post by Díaz
I tried the backslash but it doesn't seem to work. I
also tried a varied number of backslashes (2, 4, etc),
to allow for Java backslash escaping, regular
expressions, etc., to no avail.

Did you try leaving the escape off :-

SELECT * FROM EMAILS_TABLE WHERE EMAIL LIKE '%_%'

Only a suggestion, I have not tried it, nor am I an expert.

Or do you have to escape it?

[Díaz]

No, the underscore in an SQL LIKE statement means 'any
single character', so appending it to a percent sign
doesn't make sense. I need to check for actual
underscore characters.

Gonzalo

Post by Mark Salter

Post by Díaz
I tried the backslash but it doesn't seem to work.

I

Post by Díaz
also tried a varied number of backslashes (2, 4,

etc),

Post by Díaz
to allow for Java backslash escaping, regular
expressions, etc., to no avail.

Did you try leaving the escape off :-
SELECT * FROM EMAILS_TABLE WHERE EMAIL LIKE
'%_%'
Only a suggestion, I have not tried it, nor am I an
expert.
Or do you have to escape it?
--
Mark

---------------------------------------------------------------

Post by Mark Salter
Mckoi SQL Database mailing list
http://www.mckoi.com/database/
To unsubscribe, send a message to

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com


---------------------------------------------------------------
Mckoi SQL Database mailing list http://www.mckoi.com/database/
To unsubscribe, send a message to mckoidb-***@mckoi.com

[Mark Salter]

Post by Díaz
No, the underscore in an SQL LIKE statement means 'any
single character', so appending it to a percent sign
doesn't make sense. I need to check for actual
underscore characters.

Fair enough I thought ? was a single character, and always specify * as
my any characters!

Apologies, but I see Stefaan provide the answer you needed!

[Stefaan A Eeckels]

On Wed, 13 Sep 2006 18:33:51 -0700 (PDT)

Post by Díaz
Does Mckoi understand escaped underscores in a LIKE
statement? For example,
SELECT * FROM EMAILS_TABLE WHERE EMAIL LIKE '%\_%'
... so that it returns all email addresses
containing underscores.

It does certainly support the escaped underscore, I tried it just now
with exactly the syntax you're giving above.

Post by Díaz
I tried the backslash but it doesn't seem to work. I
also tried a varied number of backslashes (2, 4, etc),
to allow for Java backslash escaping, regular
expressions, etc., to no avail.

Aha - you're trying to do this using ODBC and Java.

Post by Díaz
If true, is there a workaround? I can use prepared
statements, but still need to write the right SQL.

Here's what works, I tried it just now:

String query = "SELECT * FROM EMAILS_TABLE WHERE EMAIL LIKE '%\\_%'";

Here's the code I tried (setup stuff omitted):

String query = "select name from users where name like '%\\_%'";
System.out.println(query);

try {
Statement stmt = edb.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
System.out.println("Name: " + rs.getString(1));
}
rs.close();
stmt.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}

It correctly retieves the users with an underscore in their name from
my test McKoi database.

Take care,
--
Stefaan

[Díaz]

Thanks, that solved my problem.

Gonzalo

Post by Stefaan A Eeckels
On Wed, 13 Sep 2006 18:33:51 -0700 (PDT)

Post by Díaz
Does Mckoi understand escaped underscores in a

LIKE

Post by Díaz
statement? For example,
SELECT * FROM EMAILS_TABLE WHERE EMAIL LIKE

'%\_%'

Post by Díaz
... so that it returns all email addresses
containing underscores.

It does certainly support the escaped underscore, I
tried it just now
with exactly the syntax you're giving above.

Post by Díaz
I tried the backslash but it doesn't seem to work.

I

Post by Díaz
also tried a varied number of backslashes (2, 4,

etc),

Post by Díaz
to allow for Java backslash escaping, regular
expressions, etc., to no avail.

Aha - you're trying to do this using ODBC and Java.

Post by Díaz
If true, is there a workaround? I can use prepared
statements, but still need to write the right SQL.

String query = "SELECT * FROM EMAILS_TABLE WHERE
EMAIL LIKE '%\\_%'";
String query = "select name from users where name
like '%\\_%'";
System.out.println(query);
try {
Statement stmt = edb.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
System.out.println("Name: " +
rs.getString(1));
}
rs.close();
stmt.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
It correctly retieves the users with an underscore
in their name from
my test McKoi database.
Take care,
--
Stefaan
--
As complexity rises, precise statements lose
meaning,
and meaningful statements lose precision. -- Lotfi
Zadeh

---------------------------------------------------------------

Post by Stefaan A Eeckels
Mckoi SQL Database mailing list
http://www.mckoi.com/database/
To unsubscribe, send a message to

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com


---------------------------------------------------------------
Mckoi SQL Database mailing list http://www.mckoi.com/database/
To unsubscribe, send a message to mckoidb-***@mckoi.com