Geek Answers Handbook

MySQL removes the last 4 letters

I need a MySQL query (if PHP is required, you can also use it), which will remove the end of all values ​​in one column named 'url'.

The problem is that I saved the URLs having .php and now I want to remove this ending from all the values ​​in the database.

Example:

old values:

 my_url.php my_sadas.php

new values:

 my_url my_sadas
+4
source share
4 answers
 UPDATE mytable SET myfield = SUBSTRING(myfield, 1, LENGTH(myfield)-4) ;

If you also want to check if the field ends with '.php' before truncating, you can add this condition:

 UPDATE mytable SET myfield = SUBSTRING(myfield, 1, LENGTH(myfield)-4) WHERE RIGHT(myfield, 4) = '.php' ;

Oh, there is also LEFT() , which can be used instead of SUBSTRING() , as well

And CHAR_LENGTH() should be used instead of LENGTH() , since it is multi-byte safe (as long as LENGTH() not):

 UPDATE mytable SET myfield = LEFT(myfield, CHAR_LENGTH(myfield)-4) WHERE RIGHT(myfield, 4) = '.php' ;
+18
source 
 UPDATE files SET fileName = SUBSTRING(fileName, 1 , (LENGTH(fileName) - 4)) ;
+4
source

In case you may have double-byte characters, you can use CHAR_LENGTH instead of LENGTH and LEFT instead of SUBSTRING .

Combining this with the desire to remove only characters if they are .php , either a CASE WHERE or WHERE .

 UPDATE table SET field = CASE WHEN INSTR(field, '.php') > 0 THEN LEFT(field, INSTR(field, '.php')) ELSE field END

or

 UPDATE table SET field = LEFT(field, INSTR(field, '.php')) WHERE INSTR(field, '.php') > 0
+2
source

SUBSTRING_INDEX is easier in this case.

 UPDATE mytable SET mytable.url = SUBSTRING_INDEX(mytable.url, '.php', 1) WHERE mytable.url LIKE "%.php"
+2
source

More articles:


All Articles