cancel
Showing results for 
Search instead for 
Did you mean: 

Materialized views - Query ends with _X

nikhil_cr
Cosmonaut

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

5 REPLIES 5

RADSr
Captain
Captain

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 ================

-- IncortaOne@PMsquare.com --

RADSr
Captain
Captain

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: 

 

RADSr_0-1705522721258.png

 

-- IncortaOne@PMsquare.com --

nikhil_cr
Cosmonaut

Thank  you

kartikgaur24
Rocketeer

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]))$'

Screenshot 2024-01-22 at 4.14.02 PM.png

 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.