none
concatenate a space into a string

    Question

  • I am using a dotnet autocomplete extender. to retrieve a client list. I want to use the first name  and last name with a space between them. THe code in C# calls the variable @name + "%" , and retrieves  the list and shortens it with each keystroke. So you type in "DAV' and all the daves and davids are listed - then if you add an E all the davids are filtered out. The problem arises when you get to the space it drops everything. I should recognize the space and let you continue with the last name. I tired firstname+' '+lastname but it doesn't work.  Is there a special character that can be used for this purpose?
    Saturday, August 24, 2013 7:25 PM

Answers

  • The problem arises when you get to the space it drops everything. I should recognize the space and let you continue with the last name. I tired firstname+' '+lastname but it doesn't work.  Is there a special character that can be used for this purpose?

    You can concatenate the multiple columns for use in the LIKE expression.  The space shouldn't matter.  For example:

    DECLARE @name nvarchar(101) = 'Aaron C';
    
    SELECT *
    FROM AdventureWorks2012.Person.Person
    WHERE
    	FirstName + ' ' + LastName LIKE @name + '%';

    I should add that a scan will be required for each keystroke, which is a consideration if your table is large.  If this isn't working for you, please provide the actual query.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, August 24, 2013 7:45 PM