CODEWITHSUNDEEP
mysqlsql
Schwann
Schwann

Reputation: 167

Replace "And" with "and" in mysql using replace query

I am using the following query in mysql: 

update TABLENAME set columnname = replace(columnname, 'And', 'and');

the purpose was to replace And with and ("A" should be lowercase). The problem is it cannot find the "And" in database, but if I use like "%And%" then it can find it along with many other ands that are part of a word or even the ones that are already lowercase.

Upvotes: 2

Views: 117

Answers (2)

simo.3792
simo.3792

Reputation: 2236

Because you only want to replace whole words of 'And' they will all have leading and trailing spaces (unless someone has some bad punctuation such as 'and,'). So include those spaces in the search - ie

update TABLENAME set columnname = replace(columnname, ' And ', ' and ');

SQL Fiddle showing your result. http://sqlfiddle.com/#!3/3f8fa/1

Upvotes: 1

Déjà vu
Déjà vu

Reputation: 28840

The replace function is case sensitive. Thus 

update TABLENAME set columnname = replace(columnname, 'And', 'and');

may update no row if no columnname has exactly 'And' in it.

LIKE, instead, is case insensitive, thus 

SELECT columnname FROM tablename WHERE columnname LIKE '%And%';

may return results, having 'And', 'and', 'AND', ... in columnname.

Upvotes: 0

Related Questions