What's the problem with CHAR(13) or perhaps CHAR(14) in TSQL patindex? As soon as I include CHAR(14) in a pattern, I get no records found. Searching for an answer, I just found my own question (unanswered) from 2009 (here: http://www.sqlservercentral.com/Forums/Topic795063-338-1.aspx).
Here is another simple test, to show what I mean:
/* PATINDEX TEST */
DECLARE @msg NVARCHAR(255)
SET @msg = 'ABC' + NCHAR(13) + NCHAR(9) + 'DEF'
DECLARE @unwanted NVARCHAR(50)
-- unwanted chars in a "chopped up" string
SET @unwanted = N'%[' + NCHAR(1) + '-' + NCHAR(13) + NCHAR(14) + '-' + NCHAR(31) + ']%'
SELECT patindex(@unwanted, @msg)
-- Result: 4
-- NOW LET THE unwanted string includ the whole range from 1 to 31
SET @unwanted = '%['+NCHAR(1)+'-'+NCHAR(31)+']%' -- -- As soon as Char(14) is included, we get no match with patindex!
SELECT patindex(@unwanted, @msg)
-- Result: 0
It is permitted.
You need to bear in mind that the ranges are based on collation sort order not character codes however so perhaps in your default collation it sorts in a position that you do not expect.
What is your database's default collation?
What does the following return?
;WITH CTE(N) AS
(
SELECT 1 UNION ALL
SELECT 9 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 31
)
SELECT N
FROM CTE
ORDER BY NCHAR(N)
For me it returns
N
-----------
1
14
31
9
13
So both characters 9 and 13 are outside the range 1-31. Hence
'ABC' + NCHAR(13) + NCHAR(9) + 'DEF' NOT LIKE N'%['+NCHAR(1)+N'-'+NCHAR(31)+N']%'
Which explains the results in your question. Character 14 doesn't enter into it.
You can use a binary collate clause to get it to sort more as you were expecting. e.g.
SELECT patindex(@unwanted COLLATE Latin1_General_100_BIN, @msg)
Returns 4 in the second query too.