How search a phone number with CONTAINS with this format : 1(111)111-1111?

asked 04 Sep '14, 06:26

Marius%20Cere's gravatar image

Marius Cere
accept rate: 0%

You should be able to double quote the phrase. This will protect the parens and keep the number intact I believe.

For example:

select * from table where contains(*, '"1(111)111-1111"')

answered 04 Sep '14, 09:23

Edgar%20Sherman's gravatar image

Edgar Sherman
accept rate: 25%

Is it possible to search partially

Example: SELECT * FROM MYTABLE WHERE CONTAINS(PhoneNumber, '"(111)111")

Because I need to search an part of phone number.

P.S.: The SQL included in example is not functionnal

(04 Sep '14, 10:12) Marius Cere

If you want to include the area code with parens I don't believe so since the parens are special to FTS. So for example if you have a number such as 1(555)444-7777 you could use contains(, '555 or 444 or 7777')

What do you mean the SQL included is not functional? Is it giving you an error? This is exactly what I used in my testing that worked well.

(04 Sep '14, 10:24) Edgar Sherman

In your example, if i search area code and first part of phone number can I use :

CONTAINS(PhoneNumber, '555 and 444'); or CONTAINS(PhoneNumber, '555 and 444*'); or CONTAINS(PhoneNumber, '555 and 444-7777');

I tried and the result does not return the record with the phone number 1(555)444-7777

(04 Sep '14, 10:50) Marius Cere

Interesting my stars are not showing up in the comments. it should have been a * before and after 555, before and after 333 and before 7777. I'll try again with escaping it

contains(*, '*555* and *444* and *7777')

(04 Sep '14, 11:19) Edgar Sherman

For the phone number 1(444)555-7777, is it possible to search with CONTAINS the (444)555 because if I use CONTAINS(PhoneNumber, '444 and 555) the result set contains a record with the phone number 1(555)888-4444

(04 Sep '14, 13:22) Marius Cere

I am not sure you will be able to directly. But I thought of a few different options.

1: Use LIKE instead of CONTAINS. May not work if you have lots of data

2: Combine LIKE and CONTAINS. Use CONTAINS to reduce the records quickly then use LIKE to do the fine work. Again may not work to well if you have lots of matches. Example: select * from (select * from table where CONTAINS(*, '*444* and *555*')) a where description like '%(444)555%'

Maybe use a different delimiter for area code instead of parens.

(04 Sep '14, 14:30) Edgar Sherman

Yes, I think Edgar's number 2. is the best solution:

SELECT * FROM table WHERE CONTAINS(PhoneNumber, '(444)555') AND PhoneNumber LIKE '%(444)555%'

I often use this combination of CONTAINS and LIKE. You can usually use the same search string for both CONTAINS and LIKE except that LIKE requires additional wildcards after/before the search string to get the desired results.

(04 Sep '14, 15:08) Marc

Thanks Edgar and Marc! That's the way I set up, I just wanted to know if there was no other way.

(05 Sep '14, 04:46) Marius Cere
showing 5 of 8 show 3 more comments
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 04 Sep '14, 06:26

Seen: 2,007 times

Last updated: 05 Sep '14, 04:46

Advantage Developer Zone Contact Us Privacy Policy Copyright Info

Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.