Let's say you have a list of names that you want to filter by, but one of the names contains an apostrophe. You would rather not remove the apostrophe and have the filter values and results displayed with the incorrect spelling to the end user, so removing the apostrophe on the front end is not an option. A filter value passed through to your query containing an apostrophe will cause your query to error out as SQL will interpret this as incomplete single quotes. Here is a way around this.
We can first set up a filter that grabs all names. Using the replace function and four single quotes followed by a * wrapped in single quotes will take all names with an apostrophe and replace the apostrophe with a star. We then want to select the name column again. Sisense for Cloud Data Teams will pass the second select column through as the displayed filter values, but then pass the first select column through to the backend query. This way the end user will see the names properly represented in the filter options, while not passing through names with an apostrophe to the backend, which will then result in error.
select replace(name, '''','*'), name from [employee_list]
Now that we have passed through the filter values to the backend and replaced any apostrophes with stars, we can then make the same apostrophe replacement to our data and compare them with the filter values that are passed through.
where [replace(name, '''','*')=Employee_Names]A quick check to the query tab shows expanded view of how the filter is passed through to the database.
The only exception to this single-quotation syntax would be for BigQuery:
replace(name, '\'','*')Happy Sisensing 🙂
