Help with Escaping Underscores in LIKE Clause in Sisense SQL Editor

[leo82]

Hello Sisense Community,

I've encountered an issue with using the LIKE clause in the Sisense SQL editor, specifically when trying to escape underscores (_) in my search pattern. I'm attempting to filter project names that start with "E_" from a table named [Workspace_data]. However, the standard SQL ESCAPE keyword seems not to be recognized, leading to an error.

Here's the query I initially tried:

SELECT a.[workspc_nm]
FROM [Workspace_data] a
WHERE a.[workspc_nm] LIKE 'E\_%' ESCAPE '\'

The error message returned by Sisense is as follows:

1 out of 1 errors
Line 3 Column 33
Found 'ESCAPE' but expected '<End of Expression>'.

Based on the SQL cheat sheet provided by Sisense, there's guidance on various operators and comparisons, but I couldn't find specific mention of handling escape characters within the LIKE clause, especially for escaping underscores.

I've attempted to adjust the query by omitting the ESCAPE clause

select a.[workspc_nm]
from [Workspace_data] a
where a.[workspc_nm] LIKE 'E\_%'

This does not throw any error, however it gives me only partial results. In my attempt to filter project names that start with "E_"  the above query filters all projects, even project names like 'Eagle', or  'Elephant'. I want only names that follow the pattern 'E_Team ABC' 'E_Team XYZ' etc

Could anyone provide insights or guidance on the correct way to escape special characters like underscores in Sisense's SQL editor? Is there an alternative approach or syntax that Sisense supports for this kind of pattern matching?

Any advice or examples from your own experiences would be greatly appreciated. Thank you in advance for your help!

Best regards,

[DRay]

Hello @leo82,

Thank you for the question. I'm going to move this to another section for better visibility. This group is for Software Developers and ComposeSDK.

[MelissaSnell]

If it is "standard" sql then this linked page explains what underscores in a LIKE statement normally mean, and how to escape them.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/wildcard-match-one-character-transact-...

--THIS SHOULD WORK
WHERE a.[workspc_nm] LIKE 'E[_]'; 

--YOU CAN ALSO TRY SPECIFYING THE ESCAPE CHARACTER INSTEAD OF BACKSLASH - LIKE THIS
WHERE a.[workspc_nm] LIKE 'E$_' ESCAPE '$';
WHERE a.[workspc_nm] LIKE 'E^_' ESCAPE '^';

-- THIS MAY ALSO WORK - THE FIRST BACKSLASH ESCAPES THE SECOND
where a.[workspc_nm] LIKE 'E\\_%'

[Benji_PaldiTeam]

Hi @leo82 ,

An alternative approach to get all project_name starting with 'E_' is using 'left' function

select a.[project_name]
from [Workspace_Data] a
where left(a.[project_name], 2) = 'E_' 

Feel free to reach out if you have further questions, we're always happy to help 🙂
[email protected] 
Paldi Solutions, Number #1 Sisense Plugins Developer

[DRay]

Hello @leo82,

Did the solution offered by @MelissaSnell or @Benji_PaldiTeam work for you? If so, please click the 'Accept as Solution' button so that other users with the same questions can find the answer faster. If not, please let us know so that we can continue to help.

Thank you.