Deal with dashes and spaces in T-SQL String functions

Question

I am searching for the occurrence of a string in a field on a table. The search statement is done through dynamic sql with my statement template looking like this.

SET @sSQL = 'UPDATE #tempProcsAndJobs SET ' + 
  @columnName + ' = 1 WHERE createStatement LIKE ''%' + 
  @columnName + '%''';

EXECUTE (@sSQL);

The above sql produces something similar to

UPDATE #tempProcsAndJobs SET LoadSummaryTotalSales = 1 
   WHERE createStatement LIKE '%LoadSummaryTotalSales%'

This approach is usually effective, until it runs into something like

UPDATE #tempProcsAndJobs SET Nightly SXe Data Transfers = 1 
  WHERE createStatement LIKE '%Nightly SXe Data Transfers%'

Then it complains about a syntax error. I've tried using CHARINDEX, and the same problem arises, the server hates spaces and dashes. I was wondering if anyone knew how to get around this.

Any help is greatly appreciated.

score 1 · Answer 1 · answered Oct 17 '14 at 21:51

You need to encapsulate your column names with square brackets, otherwise SQL Server will interpret the spaces and dashes as part of the statement instead of the column name.

SET @sSQL = 'UPDATE #tempProcsAndJobs SET [' + 
  @columnName + '] = 1 WHERE createStatement LIKE ''%' + 
  @columnName + '%''';

EXECUTE (@sSQL);

This will result in the following statements from your example:

UPDATE #tempProcsAndJobs SET [LoadSummaryTotalSales] = 1 
   WHERE createStatement LIKE '%LoadSummaryTotalSales%'

and

UPDATE #tempProcsAndJobs SET [Nightly SXe Data Transfers] = 1 
  WHERE createStatement LIKE '%Nightly SXe Data Transfers%'

both of which are valid SQL.

Thanks, I always thought that bracket encapsulation was for database objects, like table names and sprocs . — Black Dynamite, Oct 17 '14 at 21:53
You're right, but columns also are considered objects by the parser. Along with logins, server names, etc. — squillman, Oct 17 '14 at 21:55

Deal with dashes and spaces in T-SQL String functions

1 Answers1