sqldb2

Validate whether a string has special characters using SQL query in DB2

there are a list of SQL queries and we need to write a query to validate if each of these queries have some special characters like * . For example I have a query as below,

Insert into PERSON (PERSONID,STATUS,DISPLAYNAME,EMPLOYEETYPE) values ('JACOB','ACTIVE','Jacob',(select * from user where userid='JACOB'))

I need to write a sql query that should validate whether this query has asterisk. Please note that this query is not present in any table, we just need to validate the above query as a text and see if they have any asterisk. I tried something like below however its resulting in a syntax error. My DB2 version is DB2/LINUXX8664 11.5

WITH Query AS (
SELECT 'Insert into PERSON (PERSONID,STATUS,DISPLAYNAME,EMPLOYEETYPE) values (''JACOB'',''ACTIVE'',''Jacob'',(select * from user where userid=''JACOB''))' AS QueryText)SELECT
CASE
    WHEN LOCATE('*' IN QueryText) > 0 THEN 'Contains *'
    ELSE 'Does not contain *'
END AS result FROM Query;
Solution

  • You could use one of the string-related functions: