SQL Server wildcard statement with dashes and underscores ISSUES

Question

I need to query a free text column for a string of characters that contain a - and a _. The column is defined as varchar(8000) - a very large column with lots of characters.

This is one string : '%OTHER MIE-Explanation of BMI%'

I tried using LIKE '%OTHER MIE-Explanation of BMI%' - no results.

I also tried using LIKE '%OTHER MIE-Explanation of BMI%' ESCAPE '-' - no results.

Tried WHERE CONTAINS (Comment, 'OTHER MIE-Explanation of BMI') - I get the following error message:

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view '#TEMP1' because it is not full-text indexed.

Please HELP!

Possible duplicate of [Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed](https://stackoverflow.com/questions/6003240/cannot-use-a-contains-or-freetext-predicate-on-table-or-indexed-view-because-it) — Amira Bedhiafi, Jun 12 '19 at 14:38
`-` has no special meaning. If you don't get any matches it means that there aren't any. Either the text or the source string isn't what you expect it to be. Perhaps there are invisible charactees, newlines or spaces that you haven't noticed — Panagiotis Kanavos, Jun 12 '19 at 14:46
`ESCAPE '-'` can only make things *worse*. It says that `-` is the escape character, it doesn't escape it. — Panagiotis Kanavos, Jun 12 '19 at 14:47
BTW no repro. Just as Tab Alleman shows, if the string is in the table, it will be returned — Panagiotis Kanavos, Jun 12 '19 at 14:52

score 2 · Answer 1 · answered Jun 12 '19 at 14:43

Your issue is not reproducible.

I ran this test:

DECLARE @Tbl TABLE (Col1 varchar(max));

INSERT INTO @Tbl (Col1) VALUES ('%OTHER MIE-Explanation of BMI%')    

SELECT * FROM @Tbl
WHERE Col1 LIKE '%OTHER MIE-Explanation of BMI%'

And the result was:

Col1
%OTHER MIE-Explanation of BMI%

If you got no results, then the string that you think is in your table isn't actually there.

score 0 · Answer 2 · answered Jun 12 '19 at 15:10

0

Try this:

col like '%OTHER MIE_Explanation of BMI%'

or:

col like '%OTHER MIE%Explanation of BMI%'

Hyphens come in different forms, of which the most familiar are the em-dash and en-dash. My guess is that some more obscure dash mark is used there.

answered Jun 12 '19 at 15:10

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Jan 09 '23 at 05:26

This is one string : '%OTHER MIE-Explanation of BMI%'

I tried using LIKE '%OTHER MIE-Explanation of BMI%' - no results.

I also tried using LIKE '%OTHER MIE-Explanation of BMI%'

with respect to above statement, there is difference between two dashes

– and -

try like operator without dash

score -1 · Answer 4 · answered Jun 12 '19 at 14:52

-1

The string IS actually there. I dumped this into Excel and separated (TEXT TO COLUMNS) and the string DOES exist. This is a very large, free-text field. The string is there.

answered Jun 12 '19 at 14:52

user2840187

1

If you're sure of that, then post a script that reproduces the issue. I have already posted one that shows the issue doesn't exist. – Tab Alleman Jun 12 '19 at 15:09

SQL Server wildcard statement with dashes and underscores ISSUES

4 Answers4