Re: type-casting and LIKE queries

On Fri, Mar 14, 2003 at 06:25:44PM +0000, James Gregory wrote:
> try something like
> where home_phone::text like '407%'

Thanks, this works great. I also tried using regexes on home_phone::text,
but they seem a bit slower than LIKE queries, for some reason (I'm
guessing this is true in general, as regexes have more possible cases to
deal with?)

The only downside seems to be that queries that start with the % character
don't make use of the text(home_phone) index. Is there a way around
this? I noticed that the ? character doesn't have this behavior, so I
tried a few things like:

SELECT * FROM test WHERE home_phone::text LIKE '??????4820'

and this does use the index, but it's a bit of a hack, especially if
you're searching on a varchar(255) column...

> or alternatively harness the immense power of algebra to achieve your
> devious ends :)

Not sure what you're implying here, unless maybe you were thinking along
the lines of:

SELECT * FROM test WHERE home_phone >= 4070000000 AND home_phone <= 4079999999

But unfortunately that won't work, as I have to be able to do searches on
any substring of the home_phone column...