Find previous & next entries in alphabetical index for a candidate

In a database, I have a string field. I want to find previous & next row in alphabetical order for a candidate string that is not in the database. One solution is to insert temporarily this candidate into the database and do an ORDER BY on 开发者_高级运维the field, but this will generate performance issues (doing an insert before each select). Do you know any other way to perform this ?

Thank you

---

Following might give you some pointers to start with.

SELECT MAX(StringField)
FROM   YourTable
WHERE  StringField < Candidate
UNION ALL
SELECT MIN(StringField)
FROM   YourTable
WHERE  StringField > Candidate

To retrieve the entire record (and assuming your StringFields are unique), you can wrap this into a subquery.

SELECT  *
FROM    YourTable yt
        INNER JOIN (
          SELECT StringField = MAX(StringField)
          FROM   YourTable
          WHERE  StringField < Candidate
          UNION ALL
          SELECT MIN(StringField)
          FROM   YourTable
          WHERE  StringField > Candidate
        ) yts ON yts.StringField = yt.StringField

Note: one optimization might be to compare on all uppercase (or lowercase) characters so Andy & ANDY have equal weight.