ArcGIS Data Pipelines - Filter by Attribute - Ignoring Strings with Apostrophes

[michelle-maps]

Hello,

I recently noticed what I believe is a bug with the Filter by Attributes tool in Data Pipelines.

I have a string field that I would like to filter my source table by. Some of the records have an apostrophe in the name (for example, Lowe's Home Improvement). 

I am able to find these records in the dropdown options in the SQL builder interface. However, when I preview or run the Data Pipeline, it doesn't return any records. 

These records do in fact live in the source dataset. Below is a screenshot of the # of records I would expect it to return (from our Snowflake environment).

Edited to Add:

It looks like (at least on the front end tooltip...) that the SQL query is properly using the double apostrophe for these records. Perhaps this logic is missing from the actual query on the backend?

[VenkataKondepati · Accepted solution]

Hi @michelle-maps,

You’re likely hitting an apostrophe/quote normalization bug—or a character mismatch.

Quick checks first:

• Copy the apostrophe from the record into a text inspector and confirm it’s ASCII ' (U+0027) and not a curly ’ (U+2019). Mixed quotes will show in the dropdown but won’t match at runtime.

• Try an ILIKE/LIKE filter to bypass the exact quote:
  NAME ILIKE 'Lowe%s Home Improvement'

• If Pipelines supports expressions, normalize then filter:
  REGEXP_REPLACE(NAME, '[\u2019\u02BC\u2032]', '''') = 'Lowe''s Home Improvement'

• Or build the literal without escaping:
  NAME = CONCAT('Lowe', '''', 's Home Improvement')

If those work but = with the raw value still returns 0, it’s a backend escaping bug. Capture the minimal repro (field, value, data source) and open a case. Meanwhile, stick to LIKE/ILIKE or do a one-step normalize → filter in the pipeline.

Regards,

Venkat

[michelle-maps]

Thanks! I checked the value and the apostrophe is in fact ASCII (U+0027).

Unfortunately Data Pipelines does not support expressions.

But I was able to use the text "contains" option which uses the LIKE function. If I enter 'Lowe%s Home Improvement', it returns the missing values. 

Thank you for the help!

[MaxPayson]

Hi @michelle-maps , the apostrophe being removed is a bug, thank you for taking the time to report it! And I'm glad you found a workaround, thanks for the help @VenkataKondepati .

We have an internal issue tracking this but if you'd like to create a Support case as well, that'd be helpful. The case will generate an official bug that others can reference and use to track the status. Here's the Support contact page.