Post reply

Matching backslash in a table's column using R language

  • August 24, 2020 at 10:00 am

    #3782855

    Given the following code:

    CREATE TABLE [dbo].[products] ( [nvarchar](25))
    INSERT INTO [dbo].[products] VALUES ('A\BCDEFG')
    GO
        
    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  FROM [dbo].[products]',
        @input_data_1_name = N'inData',
        @output_data_1_name = N'outData'
    WITH
        RESULT SETS (AS OBJECT [dbo].[products]);
    GO

    why is the data being output when the <backslash> character should be filtered by the "\x5c" pattern?

    Using data which matches the other exclusions (<quote>, <apostrophe>, <comma>, <question mark>, <backtick>) all gets removed, it is only the <backslash> that's not working.

    I know that <backslash> is a special character but:

    SELECT ASCII(SUBSTRING([value], 2, 1)) FROM [dbo].[products]

    returns 92, so the <backslash> is being identified as a character in its own right.

    Thanks.

  • August 24, 2020 at 12:05 pm

    #3782904

    Note: I've managed to make this work by changing "pattern2" to:

    pattern2 = "[\x22\x27\x2c\x3f\x5c\x5c\x60]"

    but I don't know why I need to escape the backslash in the pattern2 value when using HEX values.

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

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