CODEWITHSUNDEEP
asp.netsql-serverdrop-down-menu
Ali_dotNet
Ali_dotNet

Reputation: 3279

SQL Server query order by column containing string

I'm filling my ASP.NET dropdownlist from a column (nvarchar) in my SQL Server database.

Is there any way that I can bring rows containing a keyword to the beginnning of my returned result set?

For instance I have these records in my table:

abc
abc1
abcd2
abcXYZ3
adfdf3XYZ
abcd5

I want to have rows containg XYZ at the top of my dropdownlist, i.e:

abcXYZ3
adfdf3XYZ
abc
abc1
abcd2
abcd5

Is it possible to create a SQL query for this purpose, for instance something like this:

select * 
from myTable 
order by (mycolumn LIKE '%XYZ%')

How can I get my desired result? If it is not possible in SQL Server, how can I do it in my C# code?

Upvotes: 2

Views: 3213

Answers (4)

Vitaliy Zaichko
Vitaliy Zaichko

Reputation: 1

-- on the Oracle 11g

select myField
  from myTable
order by instr(myField, 'XYZ') desc

Upvotes: 0

dash
dash

Reputation: 91480

You could order by the number of occurences:

SELECT column,
       len(column) - len(replace(column,@text,'')) AS MatchNumber

FROM   table

ORDER BY len(column) - len(replace(column,@text,''))

This has the advantage of ordering by the number of times a match occurs, but is biased towards longer strings with more matches.

Upvotes: 2

UnitStack
UnitStack

Reputation: 1185

Split it out, add the items that contain keywords first and then after add the others.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453243

To do it in SQL you could use

SELECT *
FROM   myTable
ORDER  BY CASE
            WHEN mycolumn LIKE '%XYZ%' THEN 0
            ELSE 1
          END,
          mycolumn

Upvotes: 8

Related Questions