0

I'm building a case in my query where if a field is either blank or has the substring 'Name Not Found' then the field in question should be replaced with the value of another field.

This is what I have so far:
CASE WHEN [Salesperson1 Name] = '' THEN [Salesperson Name (Primary)]
WHEN CONTAINS([Salesperson1 Name], 'Name Not Found') THEN [Salesperson Name (Primary)]
ELSE [Salesperson1 Name]
END,

but when I run the query I get this message:
Msg 7601, Level 16, State 2, Line 19
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'staging_leads_rr' because it is not full-text indexed.

How could I get this done? What am I doing wrong? I'm not sure what the error message means, because I'm new to writing stuff in T-SQL.

lsward
  • 45
  • 1
  • 1
  • 10

2 Answers2

0

You could try replacing the call to CONTAINS with the LIKE operator:

CASE WHEN [Salesperson1 Name] = ''
     THEN [Salesperson Name (Primary)]
     WHEN [Salesperson1 Name] LIKE '%Name Not Found%'
     THEN [Salesperson Name (Primary)]
ELSE [Salesperson1 Name]
END

If you really want to use CONTAINS then you will have to create a full text index on the [Salesperson1 Name] column, q.v. this helpful SO post.

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

The Contains function relates to the Full-Text search functionality of SQL Server which is more suited for large scale text processing. See reference here: https://msdn.microsoft.com/en-us/library/ms142571.aspx

In order to do this simply you should use something more like the charindex function, which returns a positive value relating to the first character position of the search string if the string is indeed found and 0 if not : https://msdn.microsoft.com/en-us/library/ms186323.aspx

Then your query would look more like this;

CASE WHEN [Salesperson1 Name] = '' THEN [Salesperson Name (Primary)]
WHEN CHARINDEX( 'Name Not Found', [Salesperson1 Name]) > 0 THEN [Salesperson Name (Primary)]
ELSE [Salesperson1 Name]
END,
Milney
  • 6,253
  • 2
  • 19
  • 33
  • Would this lead to false positives if a sales person's name started with 'N'? – lsward Jun 17 '16 at 14:12
  • The whole string must be matched otherwise 0 is returned. I have just verified this using [SELECT CHARINDEX( 'Name Not Found', 'NOT the string we are looking for')] which returned 0. So this would only be an issue if the string 'Name Not Found' appeared somewhere within the sales persons name (which seems unlikely :) ) – Milney Jun 17 '16 at 14:17