SQL remove part of string

You could write a little "name cleaner" procedure in c# that would strip these elements, and then sort the list by this stripped version and then by the date. You could also perform this in the sql query side by a series of nested replaces. Finally, as you mentioned, you could attempt to clean the entries in the database (possibly by creating another field for the cleaned name).

Which method you choose would be determined by how much data you are dealing with, and how often you need to do something like this. If this is a lot of data, and you can see needing this in other future applications, I would recommend handling this at the database level. You could write a function in SQL that formats the names, and then decide if you want to use it at query time or when inserting the data.

The function might look something like this:

drop function [fn_formatName]
go
CREATE FUNCTION [dbo].[fn_formatName] 
(
    @Name nvarchar(4000)
)
RETURNS nvarchar(4000)
AS
BEGIN
    set @Name = replace(@Name, '.', '')
    set @Name = replace(@Name, '  ', ' ')

    if(len(@name) > 4) begin
        set @Name = replace(Left(@Name, 4), 'Mrs ', '') + Right(@Name, Len(@Name) - 4)
    end if(len(@name) > 4) begin
        set @Name = replace(Left(@Name, 4), 'Dr ', '') + Right(@Name, Len(@Name) - 4)
    end if(len(@name) > 4) begin
        set @Name = replace(Left(@Name, 4), 'Mr ', '') + Right(@Name, Len(@Name) - 4)
    end if(len(@name) > 4) begin    
        set @Name = replace(Left(@Name, 4), 'Ms ', '') + Right(@Name, Len(@Name) - 4)
    end

    set @Name = ltrim(@Name)

    RETURN @Name
END

And then your query would look like this

SELECT  [ListMP]
      ,[Name]
      ,dbo.fn_formatName([Name]) as 'CleanName',
      ,[Address1]
      ,[City]
      ,[State]
      ,[ZipCode]
      ,[Date]
      ,[OrderCode]
      ,[SequenceNbr]
  FROM [Customer].[dbo].[Orders]

  ORder by [OrderCode], CleanName, [Date]

you could use c# to clean up the name like so:

string FixedName(string name)
{
    name = name.Trim();
    var prefixes = new string[] { "Mrs. ", "Mrs ", "Mr. ", "Mr ", "Dr. ", "Dr " };
    foreach (var prefix in prefixes)
    {
        if (name.StartsWith(prefix, true, CultureInfo.InvariantCulture))
        {
            name = name.Substring(prefix.Length).Trim();
            break;
        }
    }
    return name;
}