Display selected string first in SQL server (database)table

Question

1.00/5 (1 vote)

See more:

Hi all

how to display selected litter first in sql server table

Please help!!!!!
Advance thanks
shakeer.

What I have tried:

query to display start with first litter (seleted letter for Example: F)

Posted 21-Jul-16 1:42am

Member 12094227

Updated 21-Jul-16 2:54am

Add a Solution

Comments

F-ES Sitecore 21-Jul-16 8:48am

In addition to the solution below you could try UNION and UNION the results where the letter starts F with those where it doesn't. You'd need to try both methods and see which one performs better as it might depend on your data.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-07-21T02:35:00

Solution 1

That's difficult - it's easy to display just the rows that begin with F:

SQL

SELECT * FROM MyTable WHERE MyColumn LIKE 'F%'

But ordering is more complex, because SQL doesn't have particularly good string handling facilities.
You can do it, but it's a cludge:

SQL

SELECT * From MyTable
ORDER BY CASE WHEN SUBSTRING(MyColumn , 1 , 1) = 'F' THEN 1 ELSE 2 END, MyColumn

Posted 21-Jul-16 2:35am

OriginalGriff

Comments

Richard Deeming 21-Jul-16 9:40am

It would probably be better to use:
ORDER BY CASE WHEN MyColumn Like 'F%' THEN 1 ELSE 2 END, MyColumn

That way, the query can use an index on the column, if there is one.

OriginalGriff 21-Jul-16 9:52am

Good thought! :thumbsup:

koolprasad2003 · Answer 2 · 2016-07-21T02:54:00

There are multiple option for it, either you can use LIKE as suggested by OriginalGriff or you can use LEFT function with CASE operator, see below snippet

SQL

set @checkWord= 'false' when LEFT(@checkWord, 1) = 'F' then 1 else 0 end

OR use CharIndex as below

SQL

set (CharIndex('f', 'false') = 1))

hope it helps