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]]
______________________________________________
[email protected] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
