Materialized views - Query ends with _X

[nikhil_cr]

Hi,

I'm trying to write a query in MV (Spark SQL) which excludes the items ending with "_X" 

I understand underscore ("_") is single character wildcard in SQL and should be addressed as '%[_]x' or '%\_x' which will include the Underscore (_).  But it seems in Incorta - this is not working properly. After using the below query I can still see items ending with '_x'

select *  from table1
where CATALOG_NUMBER not like '%\_x'

or CATALOG_NUMBER not like '%\_X'

Please suggest any other method or if i'm missing anything above?

Thanks,

Nikhil

-nikhil.cr@lkqeurope.com

[RADSr]

I found this ( below ) w/ a Google search.

Alternatively I'd bring the data into an insight and fiddle w/ a formula filter  ( or start with "ends with" ) and then copy the SQL from the insight into the MV and see if replicates the results.

HTH,

=============== snip ================== source: https://www.mssqltips.com/sqlservertip/5670/examples-and-function-for-using-sql-server-like-operator...

To get around this, we can use the ESCAPE clause with the SQL LIKE operator to tell the query engine to use the wildcard character as a literal.

The ESCAPE clause has the following format:

ESCAPE 'escape_character'

For instance, in the following query the escape character is '!', and it is also included in the pattern. It means the symbol following the escape character should be considered as a regular symbol:

USE TestDBGO

SELECT *FROM myUserWHERE LoginName LIKE '%!_my%' ESCAPE '!'

Therefore, '_' symbol, which is after '!' in the pattern, is not considered a wildcard character and we have this result:

So, after applying the ESCAPE clause to the example with a variable we have the correct result:

=========== end snip ================

[RADSr]

Update - I uploaded a quick test file, did the query in an insight and then copied the SQL from the insight into the MV using SPARK SQL and it appears to accept the underscore as a literal character and not a wildcard: 

[nikhil_cr]

Thank  you

[kartikgaur24 · Accepted solution]

There is a simpler solution to this issue. You can use negative lookbehind regex notation.

SELECT
t1.col1
FROM
table1 t1
WHERE
t1.CATALOG_NUMBER regexp '.+(?<!(_[xX]))$'

 This will look for "_" followed by any letters present inside the square bracket, in this case [xX]. So it will reject all the strings which fulfil this condition.