[R] Matching backslash in a table's column using R language

[Peter Bishop]

In SQL, I'm using R as a way to filter data based on:
    - 20 characters in the range <space> to <tilde>
    - excluding <quote>, <apostrophe>, <comma>, <question mark>, <backslash>, <backtick>

Given a SQL column containing the data:

    code
    ----
    A\BCDEFG

and the T-SQL script:

    EXEC [sys].[sp_execute_external_script]
            @language=N'R',
            @script=N'
    pattern1 = "^[\x20-\x7e]{1,20}$"
    pattern2 = "[\x22\x27\x2c\x3f\x5c\x60]"

    outData <- subset(inData, grepl(pattern1, code, perl=TRUE) & !grepl(pattern2, code, perl=TRUE))',
            @input_data_1 = N'SELECT [code] FROM [dbo].[products]',
            @input_data_1_name = N'inData',
            @output_data_1_name = N'outData'
    WITH
            RESULT SETS (AS OBJECT [dbo].[products]);
    GO

why does the row detailed above get returned? I know that backslash is a special character but not in the SQL table. Consequently, the T-SQL code:

    SELECT ASCII(SUBSTRING([value], 2, 1)) FROM [table]

returns 92 (the ASCII code for <backslash>) which shows that this is being recognised as a backslash character and not as an escape indicator for the following "B".

Can anyone advise how I can filter out the <backslash> in the way that the other identified characters are being successfully filtered? As the data is being retrieved from a table, I can�t ask the data provider to use �\\� instead of �\� as that will be invalid for other uses.

Thanks.

	[[alternative HTML version deleted]]