I have an Oracle table which has a column called sql_query. All the SQL queries are placed in this column for all the records in that table.
Now, I see many records which have NO SPACE before WHERE in WHERE clause. I need it because the field hits several web forms and I get a syntax error.
Ex:
Select * from haystackwhere id = 3;
How do find these kind of records and replace them with a space like this:
Select * from haystack where id = 3;
Thanks!!
An easy way is to just use the like operation, assuming that your SQL queries are all simple (such as having a single where clause):
select *
from table t
where sql_query like '%where %' and sql_query not like '% where %';
You can readily turn this into an update:
update table
set sql_query = replace(sql_query, 'where ', ' where ')
where sql_query like '%where %' and sql_query not like '% where %';
This query will look for any instances of the column that don't have the word 'WHERE' with a space before it. Then, if the word WHERE exists in those records, it will update it to '(space) WHERE'
UPDATE Table
SET SqlQuery = REPLACE(SqlQuery, 'WHERE', ' WHERE')
WHERE SqlQuery NOT LIKE '% WHERE%'
This will only work if your SQL queries do not have WHERE anywhere else in the query.
For example, if you have a field named WHERE_LOCATION in the query, this will not work.
To replace the SQL query directly:
--YOUR QUERY:
select replace(replace(replace('Select * from haystackwhere id = 3;',
'where', ' where '), 'WHERE', ' WHERE '), 'Where', ' Where ') from dual;
--If you have WHERE encoded as another case, E.G. wHere, modify appropriately.
To replace using PL/SQL:
DECLARE
CURSOR C1 IS SELECT SQL_FIELD_COLUMN FROM YOURTABLE;
NEW_SQL VARCHAR(1000);
BEGIN
FOR R1 IN C1
LOOP
--WILL NOT WORK IF SQL_FIELD_COLUMN CONTAINS COLUMNS NAMED WHERE_LOCATION, ETC.
--REMEMBER TO Change replacements for other cases, E.G. wHere
--
NEW_SQL := replace(replace(replace(R1.SQL_FIELD_COLUMN,
'where', ' where '), 'WHERE', ' WHERE '), 'Where', ' Where ');
--NOW UPDATE THE TABLE/S CONTAINING YOUR SQL
END LOOP;
END;
