Sort a column by ignoring the leading English articles


Here is an example to sort a column by ignoring the leading articles:

declare @t table (id int, fieldname varchar(2000))

insert

into @t values (1, ‘A headache In Iraq’)

insert

into @t values (2, ‘A Fighting Man’)

insert

into @t values (3, ‘The Region Map’)

insert

into @t values (13, ‘the Joyful UN’)

insert

into @t values (14, ‘an Apple Tree’)

insert

into @t values (15, ‘The butiful land’)

SELECT

*

–, SUBSTRING(fieldname, charindex(‘ ‘,fieldname), len(fieldname))

FROM

@t

ORDER

BY CASE WHEN substring(fieldname, 0, charindex(‘ ‘, fieldname))=‘A’

OR

substring(fieldname, 0, charindex(‘ ‘, fieldname))=‘An’

OR

substring(fieldname, 0, charindex(‘ ‘, fieldname))=‘The’

THEN

substring(fieldname, charindex(‘ ‘, fieldname), len(fieldname))

ELSE

fieldname END

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s