SQL select statement - returning records starting with variable length string

Question

I am using an alphabetical sorting feature and need a SQL statement to return records beginning with a variable length string. However, records also need to be returned if there are periods, spaces, or dashes between any of the characters in the string.

For example, the value passed in could be "M" (easy). Or "MA" (in which case it needs to return records starting with "MA", "M.A", "M A", and "M-A"). Or "MAA", and so on.

This is the statement I have so far:

"SELECT * from table where LEFT(name," + value.Length + ")='" + value + "'"

But I can't work out how to get it to return results where there are periods, spaces or dashes in name. Any help constructing the statement would be great.

Answer 1

Looks like a job for Regular Expressions. You can do it by returning some matching records in and applying Regex in ASP.NET & LINQ...or more efficiently, you can apply it to your SQL statement.

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Answer 2

If you're on MS SQL Server you could use the replace function if you have a reasonably finite set of "special" characters. You'll ruin index usage, but you'll likely do that anyway. For example:

SELECT
    name,
    i_will,
    never_use,
    select_star
FROM
    My_Table MT
WHERE
   REPLACE(REPLACE(REPLACE(name, '.', ''), ' ', ''), '-', '') LIKE @prefix + '%'

As you can see though, the statement quickly gets unwieldy as you add "_", "(", ")", etc...