Using LIKE against a computed column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using LIKE against a computed column?

Author

Topic

littlewing
Starting Member

33 Posts

Posted - 2006-06-22 : 09:10:16

Hi I have a simple lookup of firstname and lastname using LIKE.

SELECT * from Customers WHERE FirstName LIKE '%' + @Key + '%' OR LastName LIKE '%' + @Key + '%'

Sample Data
id firstname lastname
1 John Smith
2 Barb Wire
3 Bob Green

All is well until someone submits the string 'John Smith' - then the search returns 0 results. Is there a way to "concatenate" the firstname and lastname columns into a "virtual" column that LIKE can search against and return 1 record when @key = 'John Smith'?

Thanks very much.

LW

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-22 : 09:15:53

Try

SELECT * from Customers WHERE FirstName+ ' '+ LastName LIKE '%' + @Key + '%'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-22 : 09:16:31

Yes.
Make virtual column formula as

LTRIM(RTRIM(LTRIM(RTRIM(FirstName)) + ' ' + LTRIM(RTRIM(LastName))))

But then you will face problem with "Smith, John".

Peter Larsson
Helsingborg, Sweden

cvraghu
Posting Yak Master

187 Posts

Posted - 2006-06-22 : 14:29:43

It would be better to get seperate search strings for First name and last name from user.

SELECT * from Customers WHERE FirstName LIKE '%' + @FKey + '%' OR LastName LIKE '%' + @LKey + '%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources