Home / server / Questions / 637912
In Process
Black Dynamite
Asked: 2014-10-18 13:48:32 +0800 CST

Deal with dashes and spaces in T-SQL String functions

  • 772

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.

sql-server

1 Answers

  1. 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.

    • 1