Naresh Aligeti Naresh Aligeti - 9 months ago 35
SQL Question

Oracle SQL String search with no spaces and replace with space

I have an Oracle table which has a column called

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


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;



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 %';