mysql - Query to replace specific character in a table -
i have table contain many fields. want want make single query update every field in table contain \' \\\'. 1 field query
update table set column1 = replace(column1, '\'', '\\\'');
but if have replace same thing in fields. have write same query every field ? there way write 1 query update fields.
q: have write same query every field? there way write 1 query update fields?
each column want update need referenced. can update multiple columns in single statement. (you don't have run separate statements; more efficient update them in 1 fell swoop.
update mytable set col1 = expr1 , col2 = expr2 , col3 = expr3
but need explicitly reference each column want update.
we can use sql statement column names. likely, we'll want exclude integer/decimal/float columns, enum, set, date, datetime, timestamp, etc. here's example of query gets column names:
select c.column_name information_schema.columns c.table_schema = 'mydatabase' , c.table_name = 'mytable'
note: backslash character escape character in mysql literals,
to represent backslash , single quote string literal
'\\'''
to represent 3 backslashes , single quote string literal
'\\\\\\'''
if wanted replace occurrences of single quote, literal backslash , single quote, i'd use expression this:
replace(foo, '''', '\\''')
when need this, reference bunch of columns, i'll use query generate expressions; this:
select concat(',`',c.column_name,'` = replace(`',c.column_name ,'`,'''''''',''\\\\'''''')') expr information_schema.columns c c.table_schema = 'mydatabase' , c.table_name = 'mytable' , c.data_type in ('char','varchar')
then can take values returned expr
, use in subsequent statement. aware of increasing number of characters in column, doesn't exceed maximum size.
Comments
Post a Comment