Oracle Contains statement with special characters-CodePudding

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.

CodePudding user response：

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

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

  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

CodePudding user response：

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('&')