Re: type-casting and LIKE queries

At 02:15 PM 3/14/03 -0500, valerian wrote:
>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...

Are you using the ? character as a wildcard? AFAIK _ and % are the wildcard
characters for LIKE.

> > 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...

Would having two indexes cover enough cases for you? One that allows
indexed LIKE '407%'. And one that allows '%4820'.

For the latter just reverse the string and index it, and do a search on
LIKE '0284%'.

Maybe you could create a function that reverses strings, not sure how that
would work tho - could look messy since you probably don't want to reverse
the % too.

Not sure if Postgresql would be able to productively use both indexes to
find a substring in the middle of text, given a suitable query. If the
substring is in a fixed position in the middle I think it can.

Regards,
Link.