Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using wildcards for Select parameters

 
jason adam
Chicken Farmer ()
Ranch Hand
Posts: 1932
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm trying to write an application where if the person puts "ab" as a query string, they will get back a result of all rows containing a Country name beginning with "Ab".
I've tried using HAVING, WHERE, LIKE, etc., but can't seem to figure how you would return this. Using a SELECT * FROM TABLE WHERE COUNTRY = 'Ab*'; doesn't work, nor does 'Ab'*;
Anyone have any ideas what I can use to get this query statement set up? Pointers to the correct information preferable over the exact Select statement I would use.
Thanks!
Jason
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1826
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use
where country LIKE < expression containing wildcard >
Spoiler: SQL wildcards are % and _. They go inside the single ticks.
[ March 24, 2002: Message edited by: Michael Matola ]
 
jason adam
Chicken Farmer ()
Ranch Hand
Posts: 1932
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
*breaks into a lovely rendition of "Wind Beneath My Wings"*
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1826
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But I'm not dead yet!
 
jason adam
Chicken Farmer ()
Ranch Hand
Posts: 1932
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One could only hope... *heavy sigh*
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jason, I think there is a little more to completely answer your question:
SELECT * FROM TABLE WHERE COUNTRY = 'ab%';
in most databases, the queries are case sensitive. So the above queries will return 'abc', 'abd', but will not return 'Abc', or 'ABc' like you originally stated. To accomlish this, you'll have to use your vendors convert to upper case function on both the column you are searching and on the search parameter itself:
in oracle:
SELECT * FROM TABLE WHERE UPPER( COUNTRY ) = UPPER( 'ab%' );
This will return any work that begins with 'ab..', 'Ab..', 'aB...', or 'AB...'
Jamie
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic