Let's assume you're loading data into a table using BULK INSERT from tab separated file. Among others you have some varchar field, which may contain any character. Content of such field is escaped with usual scheme:

• '\' as '\\';
• char(13) as '\n';
• char(10) as '\r';
• char(9) as '\t';

But now, after loading, you want to unescape content back. How would you do it?

Notice that:

• '\t' should be converted to a char(9);
• '\\t' should be converted to a '\t';
• '\\\t' should be converted to a '\' + char(9);

It might be that you're smart and you will immediately think of correct algorithm, but for us it took a while to come up with a neat solution:

declare @value varchar(max);

set @value = ...

-- This unescapes the value
set @value =
  replace
  (
    replace
    (
      replace
      (
        replace
        (
          replace(@value, '\\', '\ '),
          '\n',
          char(10)
        ),
        '\r',
        char(13)
      ),
      '\t',
      char(9)
    ),
    '\ ',
    '\'
  );

Do you know a better way?