Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    46

    Question Unanswered: Strip out "The" from alphabetized search results?

    I am not aware of this if it exists in MS SQL server. But I need to return results in alphabetic order ignoring "The" if it's the first word of a title...

    so for example title "The Cliffhanger" would be returned along with other titles that start with letter C, but "The" also must be returned as part of the title, but just ignored while alphabetizing.

    I'm sure that I'm not the first one to ever need this and don't want to re-invent the wheel, so if you have any ideas as to the best way to accomplish this, help me out.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Perhaps:
    Code:
    SELECT Col1
    FROM MyTable
    ORDER BY CASE WHEN Col1 LIKE 'The %' THEN SUBSTRING(Col1, 5, Len(Col1)-5) ELSE Col1 END
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2003
    Posts
    46
    Thanks!!!

    That seems to work perfectly - do you happen to know if this will be a HUGE performance hit, my database is not that big (about 10k records) and I'll never be returning more then a 1000 at a time (this is for a web based application)

  4. #4
    Join Date
    May 2002
    Posts
    299
    You can do this:

    [code]
    select *
    from tb
    order by case when lower(title) like 'the %' then stuff(title,1,4,'') else title end asc

    [code]
    --
    -oj
    http://www.rac4sql.net

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by lito
    do you happen to know if this will be a HUGE performance hit, my database is not that big (about 10k records) and I'll never be returning more then a 1000 at a time (this is for a web based application)
    Both solutions will, i believe (mine certainly will), prevent use by SQL Server of any index on the column so in theory yes. But 10k records is not huge so it may not be too noticable.

    Depends entriely on your app but would storing the data as
    'Data, The' be a consideration?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or an indexed view could do it I guess.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2003
    Posts
    46
    Quote Originally Posted by pootle flump
    Depends entriely on your app but would storing the data as 'Data, The' be a consideration?
    Unfortunatley not, the data is already in there. If this become a problem, I will create a "Ttitle prefix field" or stick it at the end like you suggested and manipulate during output.

    Thanks for the quick advice!

  8. #8
    Join Date
    May 2002
    Posts
    299
    If you have a where clause and filter on the indexed column, an index seek could be used.

    e.g.

    select title,title_id
    from pubs..titles
    where title_id<'c'
    order by case when lower(title) like 'the %' then stuff(title,1,4,'') else title end asc
    --
    -oj
    http://www.rac4sql.net

  9. #9
    Join Date
    Nov 2003
    Posts
    46
    Quote Originally Posted by oj_n
    If you have a where clause and filter on the indexed column, an index seek could be used.

    e.g.

    select title,title_id
    from pubs..titles
    where title_id<'c'
    order by case when lower(title) like 'the %' then stuff(title,1,4,'') else title end asc
    Thanks for the advice, never used stuff() function before. I'm sure I will now.

    EDIT: BTW this does not return desired result. (I asumed title_id<'c' should be title<'c') Result is alphabetized by first letter of the word, and the case statement seems to be only executed after the fact.
    Last edited by lito; 01-16-06 at 20:54.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •