[thelist] SQL Query

Dougal Campbell dougal at gunters.org
Mon Aug 18 16:18:10 CDT 2003


On Mon, 18 Aug 2003, rudy wrote:

> > Select just the two-dot values:
> >
> >   SELECT * from mytable WHERE myfield LIKE "%.%.%"
> >     AND NOT myfield LIKE "%.%.%.%"
>
> good thinking, dougal, but try your query on some test data like this --
>
>   1.onedot
>   2.two.dots
>   3...threedotsinarow
>   4.four.dots.oops.i.lied.theres.more
>   2.22.222.a.lot.of.dots.in.this.one.too
>
> i like tab's solution -- very elegant -- although it does depend on a
> REPLACE function which maybe not every database has
>
> i'm sure there's a regex solution to the original problem but few databases
> support regular expressions in sql
>
> mysql being one that does   ;o)

Right, I realize that if there are other patterns besides the two
mentioned that it would complicate things. But if those two patterns are
the only two to be concerned with, using the LIKE clauses will be the
most platform-independent method.

As you say, certain db platforms may have non-standard functions to make
the matching easier and more-specific. But since the original poster
didn't mention a platform, I just gave a solution that would work with
the information we *did* have. :)

-- 
Ernest MacDougal Campbell III, MCP+I, MCSE <dougal at gunters.org>
http://dougal.gunters.org/             http://spam.gunters.org/
  Web Design & Development:  http://www.mentalcollective.com/
       This message is guaranteed to be 100% eror frea!


More information about the thelist mailing list