CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
giosakti
giosakti

Reputation: 411

Sorting (or usage of ORDER BY clause) in T-SQL / SQL SERVER without considering some words

i'm wondering whether it is possible to use ORDER BY clause (or any other clause(s)) to do sorting without considering some words.

For ex, article 'the':

  1. Bank of Switzerland
  2. Bank of America
  3. The Bank of England

should be sorted into:

  1. Bank of America
  2. The Bank of England
  3. Bank of Switzerland

and NOT

  1. Bank of America
  2. Bank of Switzerland
  3. The Bank of England

Upvotes: 3

Views: 1271

Answers (5)

Lakedaimon
Lakedaimon

Reputation: 1934

My Solution a little bit shorter

DECLARE @Temp TABLE ( Name varchar(100) );

INSERT INTO @Temp (Name)
SELECT 'Bank of Switzerland'
UNION ALL
SELECT 'Bank of America'
UNION ALL
SELECT 'The Bank of England'

SELECT * FROM @Temp
ORDER BY LTRIM(REPLACE(Name, 'The ', ''))

Upvotes: 1

Devin Burke
Devin Burke

Reputation: 13820

No, not really because the is arbitrary in this case. The closest you can do is modify the field value, such as below:

SELECT   field1
FROM     table
ORDER BY REPLACE(field1, 'The ', '')

The problem is that to replace two words, you have to next REPLACE statements, which becomes a huge issue if you have more than about five words:

SELECT   field1
FROM     table
ORDER BY REPLACE(REPLACE(field1, 'of ', ''), 'The ', '')

Update: You don't really need to check if the or of appears at the beginning of the field because you are only wanting to sort by important words anyway. For example, Bank of America should appear before Bank England (the of shouldn't make it selected after).

Upvotes: 2

aF.
aF.

Reputation: 66697

select * from #test
order by
case when test like 'The %' then substring(test, 5, 8000) else test end

Upvotes: 2

MatBailie
MatBailie

Reputation: 86716

You need to encode a method of turning one string into another and then ordering by that.

For example, if the method is just to strip away starting occurances of 'The '...

ORDER BY
  CASE WHEN LEFT(yourField, 4) = 'The ' THEN RIGHT(yourField, LEN(yourField)-4) ELSE yourField END

Or, if you want to ignore all occurrences of 'the', where ever it occurs, just use REPLACE...

ORDER BY
  REPLACE(yourField, 'The', '')

You may end up with a fairly complex transposition, in which case you can do things like this...

SELECT
  *
FROM
(
  SELECT
    <complex transposition> AS new_name,
    *
  FROM
    whatever
)
  AS data
ORDER BY
  new_name

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

If you have a limited number of words that you wish to eliminate, then you might be able to remove them by judicious use of REPLACE, e.g.

ORDER BY REPLACE(REPLACE(' ' + Column + ' ',' the ',' '),' and ',' ')

However, as the number of words add up, you'll have more and more nested REPLACE calls. In addition, this ORDER BY will be unable to benefit from any indexes, and doesn't cope with punctuation marks.

If this sort is frequent and the queries would otherwise be able to benefit from an index, you might consider making the above a computed column, and creating an index over it (You would then order by the computed column).

Upvotes: 2

Related Questions