March 15, 2012 at 7:57 am
Hi,
I need your help on the following query that I want to build.
I have a table with the following columns:
ID, Code, Description
And I have a user typing in a keyword "keyword" that I need to search into "Description". The requirement is that I bring the results sorted in the following way: First the matching descriptions that start with the keyword given and then the matching descriptions that contain the keyword given. Also, inbetween the two groups, the keywords need to be alphabetically ordered.
So, for example, if I have the descriptions:
Aberlin
Berlin
Berlina
Zedberlin
Youberlin
Irrelevant
MoreIrrelevant
and the keyword is "ber", the query should return:
Berlin
Berlina
Aberlin
Youberlin
Zedberlin
Is there an SQL statement that can help me out here?
Thanks in advance
Panayotis
March 15, 2012 at 8:26 am
Please, read the article from the link at the bottom of my signature
-- to help your helper you should provide
-- "I have table like that:" like that:
create table #City (CityName Varchar(50))
insert #City
select 'Aberlin' union all
select 'Berlin' union all
select 'Berlina' union all
select 'Zedberlin' union all
select 'Youberlin' union all
select 'Irrelevant' union all
select 'MoreIrrelevant'
-- here is an idea of how you can do
-- this sort of thing
declare @keyword varchar(50)
set @keyword = 'ber'
select CityName
from #City
where CityName like '%' + @keyword + '%'
order by (case when CityName like @keyword + '%' then 1 else 2 end)
,CityName
March 15, 2012 at 8:26 am
Hi
Does do what you need?
DECLARE @temp AS TABLE
(City VARCHAR (20))
INSERT INTO @Temp
SELECT'Aberlin'
UNION ALL SELECT'Berlin'
UNION ALL SELECT'Berlina'
UNION ALL SELECT'Zedberlin'
UNION ALL SELECT'Youberlin'
UNION ALL SELECT'Irrelevant'
UNION ALL SELECT'MoreIrrelevant'
SELECT *
FROM @Temp
WHERE
City Like '%ber%'
ORDER BY
CHARINDEX('ber',City)
,City
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
March 15, 2012 at 8:31 am
Eugene Elutin (3/15/2012)
Please, read the article from the link at the bottom of my signature
-- to help your helper you should provide
-- "I have table like that:" like that:
create table #City (CityName Varchar(50))
insert #City
select 'Aberlin' union all
select 'Berlin' union all
select 'Berlina' union all
select 'Zedberlin' union all
select 'Youberlin' union all
select 'Irrelevant' union all
select 'MoreIrrelevant'
-- here is an idea of how you can do
-- this sort of thing
declare @keyword varchar(50)
set @keyword = 'ber'
select CityName
from #City
where CityName like '%' + @keyword + '%'
order by (case when CityName like @keyword + '%' then 1 else 2 end)
,CityName
Looks like you beat me to it Eugene!
Execution plans look identical on this small subset of data as well..
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
March 15, 2012 at 8:39 am
Thank you very much. It does what I want.
However, I do not understand why and how?
If you have any link that you can direct me to in order for me to learn, I would appreciate.
Thanks again.
Panayotis
P.S. I will read the article and next time I will be quite more correct on how I am posting here.
March 15, 2012 at 8:44 am
Andy, actually, use of CHARINDEX for ORDER BY here will not satisfy OP requirements. Try to add another City like that:
UNION ALL SELECT'Zberlin'
You query will place it straight after Aberlin, while OP requirements state that if the word contains a search-word, it should be sorted it alphabetical order...
Also, using CASE WHEN in ORDER BY, shows how almost any custom order can be achieved in T-SQL.
March 15, 2012 at 8:56 am
However, I do not understand why and how?
If you have any link that you can direct me to in order for me to learn, I would appreciate.
Ok.
Both samples performs search using LIKE with wild-card, filtering out all cases where City name contains search-word at all.
Then:
My sample uses CASE WHEN statement to evaluate "sort order business rule" and give it a "priority order". If the CityName start with the search-word (LIKE 'ber%') it gives "priority order" of 1, else (the only option left, the city name just contains a search-word) it gives the "priority order" of 2, then it sort also by city name itself.
So, as result you have : All cities which name starts with search-word sorted alphabetically, then all cities which name contains the search-word, also sorted alphabetically.
You can see that using CASE WHEN can be used to enforce any sorting rule, for example lets say that you want to do sort as you have but, if a city name has a dash in it you want to see it first. You can do it by simply adding another condition into CASE WHEN:
WHEN CHARINDEX('-',CityName) != 0 THEN -1 -- making it to be sorted first!
The second sample uses CHARINDEX function which returns the position of a search-word within a CityName. It works for your set of sample data, but doesn't satisfy your sorting requirements (see my previous post). As it will sort in the following order:
Berlin - CHARINDEX('ber',City) will return 1
Aberlin - CHARINDEX('ber',City) will return 2
Zberlin - CHARINDEX('ber',City) will also return 2
Aaberlin - CHARINDEX('ber',City) will return 3
March 15, 2012 at 9:55 am
Eugene Elutin (3/15/2012)
Andy, actually, use of CHARINDEX for ORDER BY here will not satisfy OP requirements. Try to add another City like that:UNION ALL SELECT'Zberlin'
You query will place it straight after Aberlin, while OP requirements state that if the word contains a search-word, it should be sorted it alphabetical order...
Also, using CASE WHEN in ORDER BY, shows how almost any custom order can be achieved in T-SQL.
Ahh your right Eugene! :blush:
Simple change to the ordering from this:
ORDER BY
CHARINDEX('ber',City)
,City
To This
City
,CHARINDEX('ber',City)
Should handle this...
I agree that the CASE statement will allow an element of flexibility however
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
March 15, 2012 at 10:10 am
Simple change to the ordering from this:
ORDER BY
CHARINDEX('ber',City)
,City
To This
City
,CHARINDEX('ber',City)
Should handle this...
Nope, it will not.
It will place Aberlin before Berlin....
March 15, 2012 at 10:14 am
Eugene Elutin (3/15/2012)
Simple change to the ordering from this:
ORDER BY
CHARINDEX('ber',City)
,City
To This
City
,CHARINDEX('ber',City)
Should handle this...
Nope, it will not.
It will place Aberlin before Berlin....
I think I need to go home, its obviously been too long a day!! :blink:
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
March 15, 2012 at 10:38 am
You can use CHARINDEX, if you really want to:
ORDER BY CAST(NULLIF(CHARINDEX('ber',City), 1) AS BIT)
,City
I'm not sure if you will like it, especially knowing that NULLIF is compiled into CASE WHEN ....
:hehe:
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy