Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

capital-letter in lowercase letters

Sven's profile image Sven posted 1 month ago in General Permalink

Hi Ansgar,

I know there is a function to search and replace for example the word 'BREMEN' to 'Bremen'. To replace each cityname for a 20 GB database is a lot of work. Is there a way to replace all citynames written in capital-letters like 'BREMEN' to only first capital-letter like 'Bremen' ?

Thanks & best regards.

ansgar's profile image ansgar posted 1 month ago Permalink

Well you can search through Search > "Find text on server", but that does not support replacements. But that's probably a good way to start: find the tables and columns which contain the word to search, and then create UPDATE queries for the found tables and columns?

Sven's profile image Sven posted 1 month ago Permalink

Hi Ansgar, I just found the following solution in web which seems to be working pretty good...

DELIMITER ||
DROP FUNCTION IF EXISTS ucwords||
CREATE FUNCTION ucwords(s VARCHAR(255)) RETURNS VARCHAR(255) BEGIN

   DECLARE cont INT UNSIGNED DEFAULT 1;
   DECLARE cadena VARCHAR(255) DEFAULT '';

   WHILE(cont <= LENGTH(s)) DO
      IF(SUBSTRING(s,cont-1,1) IN (' ','/',',','-')) THEN
         SET cadena = CONCAT(cadena,UCASE(SUBSTRING(s,cont,1)));
      ELSE
         SET cadena = CONCAT(cadena,LCASE(SUBSTRING(s,cont,1)));
      END IF;
      SET cont = cont + 1;
   END WHILE;

   RETURN cadena;
END;
||
DELIMITER ;

UPDATE addresses SET city = ucwords(city);

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.