Special characters
-
I want to query and return records with special chars except the ones stated in the Where Clause. I tried this query below but this is the data it is returning...something does not look right:
Select *
from [XLSFiles]
where fullimagepath like '%[^a-zA-Z0-9.&_-#$\\() ]%'[\code]
Results
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\A\AC58-1153\ABG CAT AND JACK D202 C3 2018 H&H ICT.XLSX_met.txt
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\A\A67B-0421\ICT 01_20_2018_Décor_74_License_Trends International_updated 2.8.18.xlsm
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\A\A280-2005\ICT TEMPLATE-2.1.18-Updated 2.9.18-Final-Final.xlsm
-
Try changing your search string to this
'%[^-a-zA-Z0-9.&_#$\\() ]%'
I believe that the hyphen was causing you problems, because it is interpreted as a range separator, unless it occurs at the beginning of the search string.The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
The underscore is a single character wildcard in LIKE expressions.
You'll need to encapsulate it in square brackets:
Select *
from [XLSFiles]
where fullimagepath like '%[^a-zA-Z0-9.&[_]-#$\\() ]%'EDIT: Oops nevermind, it is already in brackets.
Phil Parkin is correct. It is the dash that is the issue. - sestell1 - Friday, April 13, 2018 10:55 AM
This did not work
- Phil Parkin - Friday, April 13, 2018 10:51 AM
Ok this got me closer. This work so far:
where fullimagepath Like '%[^-a-zA-Z0-9.&_#$\\()@;+, ]%'\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\9\9B2C-4223\NW All League Bedding ICT[2][1].xlsm
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\9\9830-4735\mother! ICT Form.xlsx
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\9\992B-1644\ICTREV~1.XLS
But I want to also exclude [ ] ! ~
and I tried adding them but didn't work. I also tried putting it this way to test it and see if it returs the ! and ~ but it returned 0 records.
where fullimagepath Like '%[^-a-zA-Z0-9.&_#$\\()@\[\];+, ]%' -
The exclamation mark and tilde are easy enough, but those square brackets are a challenge. I cheated by doing this:
WHERE REPLACE(REPLACE(w.SomeText, '[', ''), ']', '') LIKE '%[^-a-zA-Z0-9.&_#$\\()~! ]%';
i.e. by removing any square brackets before doing the search. It's a bit dirty though.The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Friday, April 13, 2018 11:26 AM
Almost there... this is the results with this query. I understand the one with the single quote (next to last line) but not the others..they should not show up. How do i add the single quote to my where line? I tried plugging it in there but it did not work for me.
where REPLACE(REPLACE(fullimagepath, '[', ''), ']', '') Like '%[^-a-zA-Z0-9.&_#$\\()@;+,~!\% ]%'
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\9\95A0-4654\Ningbo Lisi Project S ICT.XLSX
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\9\95A0-4654\Ningbo Lisi Project S ICT.XLSX_met.txt
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\B\B6BE-4216\WWW D21 C4 2017 Costing 1.1 Playing with Plaid Capsule(1)[1][1].xlsx
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\3\3AA2-4621\Copy of Home Electronics ICT Dec`17.xlsm
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\7\789A-0900\Ningbo Lisi Project S ICT.xlsx -
Interesting... it looks like escaping the square brackets only works when you explicitly specify the escape character for some reason?
Give this a shot:
DECLARE @TEXT TABLE
(
ID INT IDENTITY(1,1),
STRING_VALUE VARCHAR(500)
)INSERT INTO @TEXT (STRING_VALUE) VALUES
('\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\9\95A0-4654\Ningbo Lisi Project S ICT.XLSX'),
('\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\9\95A0-4654\Ningbo Lisi Project S ICT.XLSX_met.txt'),
('\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\B\B6BE-4216\WWW D21 C4 2017 Costing 1.1 Playing with Plaid Capsule(1)[1][1].xlsx'),
('\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\3\3AA2-4621\Copy of Home Electronics ICT Dec`17.xlsm'),
('\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\7\789A-0900\Ningbo Lisi Project S ICT.xlsx')SELECT *
FROM @TEXT
WHERE STRING_VALUE LIKE '%[^-a-zA-Z0-9.&_#$\\()~! `\[\]]%' ESCAPE '\' - sestell1 - Friday, April 13, 2018 12:52 PM
That works when i run your code...but when i run it on my data...
Running this :
WHERE fullimagepath LIKE '%[^-a-zA-Z0-9.&_#$\\()@+ `\[\]]%' ESCAPE '\'Getting this results....
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\D\DA22-1448\Spring 2018 SNP, Del. 3 ICT (5.6.2018 set).xlsm
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\D\DA11-1202\Copy of C3 KP- Unique - ICT!!.xlsx
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\4\4799-5310\ICT - J.Campione, MP Garlic Breadstick and Knots.xlsm
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\A\AB64-2323\ICT Form - Home Electronics, LG OLED C8 for TGT.com, 2.8.18.xlsm
\\USATL02ERAS40\auditfiles\TargetBuyerFilesCorp\Email Extraction\ICTF17\A\A836-5615\D21-C1 18-WWW ICT Target Updated 2017-10-6[1].xlsm -
Three or those are included due to commas in the name, which have not been excluded by the pattern. I think you are past the problem of special characters in your pattern string. You'll have to add in any characters you want to allow, as I can only guess at what you would want to include/exclude.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply