Escape a period character in an SQL query

Question

EDIT: Just realised that the reason for the additional results is down to another line in the query! Don't think I have enough rep to close this question.

I'm editing some existing SQL code which is searching a Lotus Notes DB.

I have this line:

@Contains(Title; "blah blah 1.5")

and I want to return only those records which contain exactly the given string in the title.

Currently it returns all records which contain "blah blah 1" so for example I get "blah blah 1.1", "blah blah 1.2" etc.

My guess is that I need to escape the '.' but I don't know how to.

Answer 1

I think you simply need to test for equality, instead of using a full text search operator:

WHERE Title = 'blah blah 1.5'

Answer 2

I'm not 100% sure, but I think the solution would be to quote the phrase you want; Something like:

@Contains(Title; "'blah blah' AND '1.5'")

At least, that is what I think you would want in MS SQL Server (though then I think the syntax would be CONTAINS(Title, '"blah blah" AND "1.5"') ).

Answer 3

In Notes/Domino formula language @Contains just does a simple substring comparison. If the second argument is a substring of (or equal to) the first argument it returns true(1), otherwise false(0). A simple equality operator would probably be more appropriate in this case.

Title="blah blah 1.5"

Answer 4

I'm assuming by your example you're actually editing a Lotus Notes search formula and not a SQL query. The SQL format for the CONTAINS function doesn't use semicolons to separate parameters, and does not have an @ sign in the function name.

Going with that assumption, you'll want to change the search formula to be simply Title = "blah blah 1.5", and in your view or search, you'll get all documents that have a Title with that exact phrase.