August 9, 2010 at 12:27 pm
Hello,
I have a requirement that customers can be searched based on various criteria. That is fine and can be done easily but the difficult part is, please see below:
Lets say customers can be searched by their lastname, firstname provided the user enters atleast 2 characters to minimize the results.
if the user enters 'sm' in the LastName then the results should look like this.
1. all the LastNames starts with 'sm%' should be listed first
3. all the LastNames contains the search criteria in the middle should list next
5. all the LastNames contains the search criteria in the last should list next
So the result should look something like this:
Smith, John
Smith, Patrick
GoldSmith, Vincent
Blacksmith, John
...
Can this be done in once single query?
Any help would be greatly appreciated.
Thanks in advance,
August 9, 2010 at 12:39 pm
I'd recommend a UNION of 3 separate SELECT statements to get the order that you want. You can add a SortOrder column to guarantee the order is right. Something like:
SELECT LastName, FirstName, 1 as SortOrder
FROM Table
WHERE [your where clause for this one]
UNION
SELECT LastName, FirstName, 2 as SortOrder
FROM Table
WHERE [your where clause for this one]
UNION
SELECT LastName, FirstName, 3 as SortOrder
FROM Table
WHERE [your where clause for this one]
ORDER BY SortOrder
You could also do a WITH/SELECT statement, but that's a bit trickier in my opinion. Does it have to be a single SELECT statement?
August 9, 2010 at 12:52 pm
create table #tbltest (firstname varchar(20),lastname varchar(20))
insert into #tbltest(firstname, lastname) values('smith','john')
insert into #tbltest(firstname, lastname) values('smith','john')
insert into #tbltest(firstname, lastname) values('tsmith','john')
insert into #tbltest(firstname, lastname) values('tsmith','john')
insert into #tbltest(firstname, lastname) values('ttsmith','john')
insert into #tbltest(firstname, lastname) values('ttsmith','john')
select * from #tbltest where firstname like '%sm%' order by firstname asc
Results...
smithjohn
smithjohn
tsmithjohn
tsmithjohn
ttsmithjohn
ttsmithjohn
-Jit
August 9, 2010 at 12:53 pm
I would probably use PATINDEX(). Something like
DECLARE @search CHAR(2)
SET @search='sm';
WITH tab AS
(
SELECT 'Smith' AS lastname, 'John' AS firstname UNION ALL
SELECT 'GoldSmith', 'Vincent' UNION ALL
SELECT 'Smith', 'Patrick' UNION ALL
SELECT 'Blacksmith', 'John'
)
SELECT *
FROM tab
WHERE lastname LIKE '%'+@search+'%'
ORDER BY PATINDEX ( '%'+@search+'%' , lastname )
Side note: due to the search condition (a wildcard search) you'll end up with a table/index scan... Also, if you really need to consider start/middle/end of the string as a sort criteria you might have to change the ORDER BY clause to
ORDER BY PATINDEX ( '%'+@search+'%' , lastname )/(LEN(lastname) +0.00) Otherwise you'll get the result sorted by absolute and not relative position.
August 10, 2010 at 6:35 am
That is exactly what i was looking for. Thank you very much Lutz. I know it can be done because I have seen somebody doing at somewhere like 3 years back in 2005 but totally forgot what was it. I think i saw that on some blog but could not find it.
Hey, I don't worry about the Table Scan now and don't have time to compare the performance between having this query and having multiple queries but at some point i will be doing it and see which one is more efficient.
Thank you for everyone input. Greatly appreciate it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply