0

I would like to search for this string 'A&G BROS, INC.' using oracle contains statement

  FROM contact
WHERE CONTAINS 
(name, 'A&G BROS, INC.') > 0

But I do not get accurate results I get over 300,000 records basically anything containing INC. I tried escaping the & char using

  FROM contact
WHERE CONTAINS 
(name, 'A&' || 'G BROS, INC.') > 0

I still get same massive results Any idea how to run this query with this special chars I want to narrow the results down so I can al least get results that starts with "A&G" Note "LIKE" and "INSTR" cannot be used.

MT0
  • 143,790
  • 11
  • 59
  • 117
zacki987
  • 1

2 Answers2

1

The & is AND, but the , is also ACCUM. The behaviour of those operators explains what you are seeing.

You need to escape those characters:

To query on words or symbols that have special meaning in query expressions such as and & or| accum, you must escape them. There are two ways to escape characters in a query expression...

So you could do:

  FROM contact
WHERE CONTAINS 
(name, 'A\&G BROS\, INC.') > 0

or

  FROM contact
WHERE CONTAINS 
(name, 'A{&}G BROS{,} INC.') > 0

or

  FROM contact
WHERE CONTAINS 
(name, '{A&G BROS, INC.}') > 0

If you can't stop your client prompting for substitution variables - which is really a separate issue to the contains escapes - then you could combine this with your original approach:

  FROM contact
WHERE CONTAINS 
(name, '{A&' || 'G BROS, INC.}') > 0
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thank you Alex for you response, I have tried all three of you suggestions I get no results at all. – zacki987 Oct 17 '22 at 17:58
  • You tagged it as Apex, so I assume you aren't actually running in a client that has at some point prompted you for a substitution variable value for `G`? – Alex Poole Oct 17 '22 at 18:03
  • I am running it on Toad and actually it does prompt me for value for G then I just pick the run literal option – zacki987 Oct 17 '22 at 18:20
  • In SQL\*Plus you can `set define off`, so don't know if Toad has an equivalent. Or you can do your original 'escape' by splitting the string after the `&`. – Alex Poole Oct 17 '22 at 19:00
  • it seems ` set define off ` not working in Toad – zacki987 Oct 17 '22 at 20:59
  • @zacki987 - I don't use Toad, but [from here](https://support.quest.com/toad-for-oracle/kb/4234144/toad-prompts-for-amp-substitution-variable-from-glogin-script), maybe try "View->Toad Options that the checkbox for prompt for substitution variables is turned off under Execute/Compile section." – Alex Poole Oct 17 '22 at 21:23
1

Another way to deal with the special characters is to use the function CHR(n), where n is the ASCII value of the special character. For &, it is 38, so instead of 'A&G BROS, INC.' you can use 'A'||CHR(38)||'G BROS, INC.' Using these special characters directly in literals can be tricky, because they can behave differently in different environments. You can find the ASCII value of a character using the ASCII function, like this:

select ascii('&') from dual;

ASCII('&')

    38
papo308
  • 11
  • 2