How to sort a string - SQL Server Forums

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

SQL Server Development (2000)

How to sort a string

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-03 : 08:43:58

Hang writes "Hi,

I have a question. I want to create a SQL query that can be sort by Title and ignoring the word "The" can be found on the first word of the string. For example:

Anna and the King
Phantom
The Matrix

And the result will be like that:

Anna and the King
The Matrix
Phantom

The Matrix will appear before Phantom because we ignore the word "The"

Thank you for helping out."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-03 : 09:01:56

SELECT Title FROM myTable
ORDER BY CASE LEFT (Title, 4)
WHEN 'The ' THEN SubString(Title, 5, 8000)
ELSE Title END

You can extend this for titles beginning with "A" and "An":

SELECT Title FROM myTable
ORDER BY CASE
WHEN LEFT (Title, 4)='The ' THEN SubString(Title, 5, 8000)
WHEN LEFT (Title, 2)='A ' THEN SubString(Title, 3, 8000)
WHEN LEFT (Title, 3)='An ' THEN SubString(Title, 4, 8000)
ELSE Title END

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-03 : 09:03:52

Replace the tablename(title) and column(titlename) with your table and column name.

select * from title
order by REPLACE(titlename,'The ','')

HTH
Jasper Smith

dsdeming

479 Posts

Posted - 2002-06-03 : 16:06:20

The problem with using replace in this context is that it will replace all occurrences of a string. Thus, for sorting purposes, 'The Red and the Black' becomes 'Red and Black' instead of 'Red and the Black'. The number of times this might actually impact the sort is probably miniscule, but Rob's solution avoids it altogether and can be easily modified to accomodate other words that need to be eliminated from the sort.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-03 : 20:51:44

I was just being lazy

- least code for requested result
Case is the way to go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources