File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes select endswith Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "select endswith" Watch "select endswith" New topic

select endswith

dibs dibley

Joined: Aug 31, 2003
Posts: 13
Is there anyway I can do something like an 'endsWith' in a Select statement to restrict the resultset that comes back from mysql?
I have a lookup on filenames, and would like to be able to restrict the select to .txt, .exe, .java, etc. etc. Is this possible, or do I have to get the whole resultset and then do an:
if (endsWith("txt")){
or something like that?

Thanks in advance,
Gul Khan
Ranch Hand

Joined: Sep 03, 2003
Posts: 173
you can use the LIKE operator in your query string.
select * from <table> where <Column> LIKE '%txt'
This will give you the rows having column ending with txt.

Gary Joehlin

Joined: Sep 18, 2003
Posts: 16
dibs dibley,
The answer given in the prior response is correct for most SQL syntax. Some implementations of the LIKE predicate (that's what the father of relational theory calls them. Just remember from high school English, when diagramming a sentence? The predicate is the 'action' taken upon the subject. And that's what most documentation calls that part of an SQL statement, the predicate) use a different 'wild-card' character.'
I've seen some use the asterisk '*', it's not an asterick, Rick wouldn't like that. Just remember from the famous speach, "I regret that i have one as-te-risk for my country". sorry for the digression, but that helps me to remember the correct pronunciation.
Some SQL implementations use one or the other, but rarely both.
If you find that the "%"- which means any character AND any number of characters in this position, returns no rows, then try the "*" in its exact place.
Keep in mind that this is not an "=" predicate, but a LIKE predicate, they are not interchangeable when looking for "patterns". The "=" predicate, of course, will check for an exact (not a pattern) match.
FYI, there are 7 predicate types in SQL, these are two of them
Best of luck and hope this expands your understanding of fundamentals of the power of SQL.
Gary Joehlin
Colorado Springs, CO
I agree. Here's the link:
subject: select endswith
It's not a secret anymore!