Searching on partial match in a text field

12 Jan 2006 7:11 PM

Andrew Chalk

I have a customer who wants to SELECT records based on a partial match in a
text field. For example, in a list of telephone numbers they want to search
for all records that contain the digits '777' in any part of the string. How
do I formulate such a query?

Many thanks.

12 Jan 2006 7:13 PM

Raymond D'Anjou

where columnName like '%777%'

Show quote

"Andrew Chalk" <ach***@magnacartasoftware.com> wrote in message
news:ejQUJw6FGHA.2064@TK2MSFTNGP09.phx.gbl...
>I have a customer who wants to SELECT records based on a partial match in a
>text field. For example, in a list of telephone numbers they want to search
>for all records that contain the digits '777' in any part of the string.
>How do I formulate such a query?
>
> Many thanks.
>

12 Jan 2006 7:20 PM

Andrew Chalk

Wow! That was fast. I don't think I even finished typing the question!

Thanks, and to "SQL" and Barry as well.

- Andrew

Show quote

"Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message
news:%23impzx6FGHA.2320@TK2MSFTNGP11.phx.gbl...
> where columnName like '%777%'
>
> "Andrew Chalk" <ach***@magnacartasoftware.com> wrote in message
> news:ejQUJw6FGHA.2064@TK2MSFTNGP09.phx.gbl...
>>I have a customer who wants to SELECT records based on a partial match in
>>a text field. For example, in a list of telephone numbers they want to
>>search for all records that contain the digits '777' in any part of the
>>string. How do I formulate such a query?
>>
>> Many thanks.
>>
>
>

12 Jan 2006 7:51 PM

Raymond D'Anjou

"Andrew Chalk" <ach***@magnacartasoftware.com> wrote in message
news:ec8pO16FGHA.3936@TK2MSFTNGP12.phx.gbl...
> Wow! That was fast. I don't think I even finished typing the question!
>
> Thanks, and to "SQL" and Barry as well.
>
> - Andrew

No problem.
This was an easy one.

12 Jan 2006 11:46 PM

ML

As your customer's requirements progress, you should look into using a
full-text index. It performs much better than the LIKE operator in most cases.

ML

---
http://milambda.blogspot.com/

13 Jan 2006 12:08 AM

Jens

This won´t help in that case becasue they do want a wildcard before
and after the searchword.

-Jens Suessmeyer.

13 Jan 2006 12:15 AM

ML

Ah, those tiny quirks of FTS. :)

ML

---
http://milambda.blogspot.com/

12 Jan 2006 7:15 PM

SQL

select * from table
where TelephoneNumber like '%777%'

http://sqlservercode.blogspot.com/

12 Jan 2006 7:16 PM

Barry

Andrew,

You can use the like keyword e.g

Select * From MyTable
Where MyColumn Like '%777%'

For a detailed explaination and further examples look up LIKE in the
Books On-Line.

HTH

Barry