Post reply

Searching removing special characters using LIKE pattern or Patindex

  • September 29, 2009 at 6:25 am

    #139216

    I'm looking for the best way of removing certain special characters below CHAR 32, from string data (nvarchar) in a table column.

    I'd like to keep some characters such as TAB (9) and CRLF (13 and 10) in my strings, but my main issue is that I'm not quite sure how and if ranges including certain characters are valid.

    Including CHAR(14) for some reason, seems to make everything fail. Here is my test code, and the results:

    SELECT COUNT(*) FROM myTable WHERE col1 LIKE '%[' + CHAR(1) + '-' + CHAR(13)+']%'

    -- Result: 344697

    SELECT COUNT(*) FROM myTable WHERE col1 LIKE '%[' + CHAR(1) + '-' + CHAR(14)+']%'

    -- Result: 0

    /* THE SAME TESTS WITH PATINDEX */

    DECLARE @findstr NVARCHAR(50)

    SET @findstr = '%[' + CHAR(1) + '-' + CHAR(13) + ']%'

    SELECT COUNT(*) FROM myTable WHERE patindex(@findstr, col1) > 0

    -- Result: 344697

    DECLARE @findstr NVARCHAR(50)

    SET @findstr = '%[' + CHAR(1) + '-' + CHAR(14) + ']%'

    SELECT COUNT(*) FROM myTable WHERE patindex(@findstr, col1) > 0

    -- Result: 0

    Why do I get a 0 result when expanding the CHAR range...?

    Best regards,

    Andreas

  • September 29, 2009 at 7:24 am

    #1059453

    I now tried to build a long string without ranges, and that seemed to work.

    DECLARE @findstr NVARCHAR(50)

    SET @findstr = '%['+CHAR(1)+CHAR(2)+CHAR(3)+CHAR(4)+CHAR(5)+CHAR(6)+CHAR(7)+CHAR(8)+CHAR(11)+CHAR(12)+CHAR(14)+CHAR(15)+CHAR(16)+CHAR(17)+CHAR(18)+CHAR(19)+CHAR(20)+CHAR(21)+CHAR(22)+CHAR(23)+CHAR(24)+CHAR(25)+CHAR(26)+CHAR(27)+CHAR(28)+CHAR(29)+CHAR(30)+CHAR(31)+']%'

    SELECT COUNT(*) FROM myTable WHERE patindex(@findstr, col1) > 0

    -- Result: 5

    Also, for my current purposes, building up ranges not including char 9, 10 and 13 results in the same 5 records:

    DECLARE @findstr NVARCHAR(50)

    SET @findstr = '%['+CHAR(1)+'-'+CHAR(8)+CHAR(11)+CHAR(12)+CHAR(14)+'-'+CHAR(31)+']%'

    SELECT COUNT(*) FROM myTable WHERE patindex(@findstr, col1) > 0

    So... I'm still not able to figure out why the SQL in my initial question does not work... probably something with Char(13)... even though it was not until I included CHAR(14) in the range that the result became 0 rows. Does anyone have an explanation for that?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply