Search for ampersand (&) when using containstable in MS SQL Server

Question

I'm using a stored procedure on a autocomplete dropdownlist to retreive a list of clients. Some of the clients have an '&' (ampersand) sign in the name e.g. 'H&M', 'Marks & Spencers' and users would like to search on the '&' sign.

When i directly use the & sign it uses it as a word breaker and does not pick the list which has '&' sign.

Is there any way i could search the table using the '&' and retreive values that have '&' sign.

Hope i've made sence explaining what i need to do.

Thanks for all your help!

This could help you: http://stackoverflow.com/questions/995478/sql-server-full-text-search-escape-characters — Knaģis, Apr 12 '13 at 09:35
Two different approaches in the answers to other questions: [changing the text before storing it in the database](http://stackoverflow.com/questions/3914798/can-i-define-which-word-breakers-to-use-when-building-a-mssql-fulltext-index), or [create a custom DLL that uses different rules for determining word boundaries](http://stackoverflow.com/questions/1542708/how-to-change-word-break-characters-in-sql-server-full-text-indexing) (more details [here](http://stackoverflow.com/questions/7123546/is-there-such-a-thing-as-third-party-sql-server-word-breaker-for-hungarian-langu)) — , Apr 12 '13 at 13:12

Edwin Stoteler · Answer 1 · 2013-04-12T11:24:23.727

2

Perhaps something like:

WHERE column LIKE REPLACE(@searchvalue, '&', '/&') ESCAPE '/'

Another idea:

DECLARE @randomstring @text

SET @randomstring ='randomstringthatwillneverbeusedforsearch'

WHERE REPLACE(column, @searchvalue, @randomstring) LIKE '%'+@randomstring+'%'

No idea about performance issues though.

edited Apr 12 '13 at 11:24

answered Apr 12 '13 at 09:55

Edwin Stoteler

1,218
1
10
25

I think you mean `column LIKE REPLACE(@searchvalue, '&', '/&') ESCAPE '/'`, but that would cause problems if the search value contains `/`. – Apr 12 '13 at 10:00
@hvd In my mind both ways do the same? How does your way perform any different? But yes then there would be problems again if there is a / in the search value. – Edwin Stoteler Apr 12 '13 at 10:03
`ESCAPE` only applies to the pattern, not to the text being searched. `'a' LIKE '\a' ESCAPE '\'` is true, `'\a' LIKE 'a' ESCAPE '\'` is false. – Apr 12 '13 at 11:07
Edited with another idea that should prevent the problem that @hvd mentioned. – Edwin Stoteler Apr 12 '13 at 11:26

Devart · Answer 2 · 2013-04-18T12:53:10.687

Try this one -

DECLARE @char CHAR(1)
SELECT @char = '&'

DECLARE @sql NVARCHAR(MAX)
SELECT @sql = (
    SELECT CHAR(13) + 
        'IF EXISTS(SELECT 1 FROM ' + 
            QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + 
            ' WHERE' + b.cols + ')  
        SELECT table_name = ''[' + s.name + '].[' + o.name + ']'' ' + c.cols +' FROM ' + 
            QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + 
            ' WHERE' + b.cols + ';' 
    FROM sys.objects o
    JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
    JOIN (
        SELECT DISTINCT p.[object_id] 
        FROM sys.partitions p
        WHERE p.[rows] > 0
    ) p ON p.[object_id] = o.[object_id]
    OUTER APPLY (
        SELECT cols = STUFF((
            SELECT 'OR CHARINDEX(''' + @char + ''', ' + QUOTENAME(c.name) + ') > 0 ' 
            FROM sys.columns c
            JOIN sys.types t ON c.user_type_id = t.user_type_id
            WHERE t.name IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
                AND c.[object_id] = o.[object_id]
            ORDER BY c.column_id
            FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '')
    ) b
    OUTER APPLY (
        SELECT cols = (
            SELECT ', ' + QUOTENAME(c.name) + ' ' 
            FROM sys.columns c
            JOIN sys.types t ON c.user_type_id = t.user_type_id
            WHERE t.name IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
                AND c.[object_id] = o.[object_id]
            ORDER BY c.column_id
            FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
    ) c
    WHERE o.[type] = 'U'
        AND b.cols IS NOT NULL
    FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)') 

    EXEC sys.sp_executesql @sql

This query searches for the substring in all db's which have tables with text fields -

DECLARE @phrase NVARCHAR(100)
SELECT @phrase = '&'

DECLARE 
      @db_name SYSNAME
    , @output NVARCHAR(200)

DECLARE db CURSOR READ_ONLY FAST_FORWARD LOCAL FOR 
    SELECT d.name
    FROM sys.databases d
    WHERE d.state_desc = 'ONLINE'
        AND d.name NOT IN ('tempdb', 'model', 'msdb', 'master')
            --AND d.name = 'your db name'
    ORDER BY d.name

OPEN db

FETCH NEXT FROM db INTO @db_name

WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @output = CONVERT(NVARCHAR(15), GETDATE(), 114) + ': Find in ' + QUOTENAME(@db_name) + ':'
    RAISERROR(@output, 0, 1) WITH NOWAIT

    DECLARE @tsql NVARCHAR(MAX) = '
    USE [' + @db_name + ']; 
    DECLARE @sql NVARCHAR(MAX)
    SELECT @sql = ''USE [' + @db_name + '];'' + (
        SELECT 
            CHAR(13) + 
                ''IF EXISTS(SELECT 1 FROM '' + 
                QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name) + 
                '' WHERE'' + b.cols + '') PRINT ''''['' + 
                s.name + ''].['' + o.name + '']'''';'' 
        FROM sys.objects o
        JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
        JOIN (
            SELECT DISTINCT p.[object_id] 
            FROM sys.partitions p
            WHERE p.[rows] > 0
        ) p ON p.[object_id] = o.[object_id]
        OUTER APPLY (
            SELECT cols = STUFF((
                SELECT ''OR CHARINDEX(''''' + @phrase + ''''', '' + QUOTENAME(c.name) + '') > 0 '' 
                FROM sys.columns c
                JOIN sys.types t ON c.user_type_id = t.user_type_id
                WHERE t.name IN (''char'', ''nchar'', ''ntext'', ''nvarchar'', ''text'', ''varchar'')
                    AND c.[object_id] = o.[object_id]
                ORDER BY c.column_id
                FOR XML PATH(''''), TYPE, ROOT).value(''root[1]'', ''NVARCHAR(MAX)''), 1, 2, '''')
        ) b
        WHERE o.[type] = ''U''
            AND b.cols IS NOT NULL
        FOR XML PATH(''''), TYPE, ROOT).value(''root[1]'', ''NVARCHAR(MAX)'') 

        EXEC sys.sp_executesql @sql
    '

    EXEC sys.sp_executesql @tsql
    PRINT REPLICATE('-', 100) + CHAR(13)

    FETCH NEXT FROM db INTO @db_name

END

CLOSE db
DEALLOCATE db

I downvoted this because it doesn't answer this question, it answers a completely different question. — , Apr 12 '13 at 11:56
Unless I'm misreading, you've extended the question to searching in all tables, and that isn't what the question asks. — , Apr 12 '13 at 12:02
You could do that. If you did, it would IMO still be taking a completely wrong approach, but it would be an answer to the question. But `o.name` will not have the value you expect. — , Apr 12 '13 at 12:07
There is a mistake. It should be written like: `WHERE s.name + '.' + o.name = 'dbo.table1'` — Devart, Jul 24 '13 at 10:21

Search for ampersand (&) when using containstable in MS SQL Server

2 Answers2