• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

select endswith

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
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")){
then
}
or something like that?

Thanks in advance,
Dibs
 
Ranch Hand
Posts: 173
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

Gul
 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
REgards,
Gary Joehlin
Colorado Springs, CO
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic