Trying to move the logic from query entity connecting to VW to Proc with input parameter and was able to create a Procedure on Maria DB . Challenge is trying to recreate includes operator logic for an column on proc using LIKE operator
but not able to achieve it 100%
currently got to an extend as below , there are multiple input paramter on WHERE clause and one below
AND ( input_parameter IS NULL OR input_parameter = '' OR REPLACE (LOWER(COLUMN_NAME),' ' , '') LIKE REPLACE (LOWER(input_parameter),' ' , '') )
additional checks made
From Appian while passing the input parameter have also converted the input parameter to lower case and have appended %% before and after the string
Input parameter definition have set charset as per the column definition.
Still issue is value with alphanumeric value ending with number fails Ex: %abc1234_def4%. No result (actual record exists), but %abc1234_def% gives results
Discussion posts and replies are publicly visible
Underscore (_) is a wildcard character in SQL LIKE patterns that matches exactly one character. When you search for %abc1234_def4%, the underscore is interpreted as a wildcard, not a literal underscore.
can you provide with equivalent replacement string to be built on appian for Example : %abc1234_def4% to be passed as input parameter for Procedure
In Appian, you only need to escape underscoresDo not make any change to DB side and give a try.Let me know if that works for you.
concat( "%", substitute(lower("abc1234_def4"), "_", "\_"), "%" )
AND ( input_parameter IS NULL OR input_parameter = '' OR REPLACE(LOWER(COLUMN_NAME), ' ', '') LIKE REPLACE(LOWER(input_parameter), ' ', '') ESCAPE '\\' )