Format only last characters of a column’s values in Mysql

Asked

Viewed 75 times

0

I wonder if it has how to format only the last characters of values in a column of my database

for example image.jpeg to image.jpg

mysql

2 answers

2


I saw an answer guiding use locate() and I’m sure SUBSTRING_INDEX is more appropriate.

  1. locate() seeks the first occurrence of an extension identifier, the ponto. but if the file has another extra point in the name, you’re chipped.
  2. SUBSTRING_INDEX however, subdivide a string into other strings. and you can choose to start left or right. Since your problem is identifying file extension, it is best to catch substrings from the last occurrence of a ponto in the text.

In this example, the command below already identifies possible situations:

    SELECT SUBSTRING_INDEX('Meu arquivo.documento.jpg', '.',-1);
    SELECT SUBSTRING_INDEX('Meu.arquivo.jpeg', '.',-1);

The result of these commands will be the file extension itself. 1. jpg 2. jpeg

Then you will mount a query to update all extensions to jpg.

    UPDATE tabela SET 
       coluna = REPLACE(coluna, SUBSTRING_INDEX(coluna,'.',-1), 'jpg');

If you want to be more judicious, then you can work only the records that are wrong:

    UPDATE tabela SET 
       coluna = REPLACE(coluna, SUBSTRING_INDEX(coluna,'.',-1), 'jpg')
    WHERE 
       SUBSTRING_INDEX(coluna, '.',-1) = 'jpeg'

0

You can make a UPDATE and use the command locate() in the WHERE. The command locate() serves for you to find a substring within a string. You could then fetch the substring ". j" and update Where substring ". j" > 0 and setting the text to the new value.

The structure of the locate command is:

LOCATE(substr,str,pos)
  • substr = the text you want to find in the string (in case jpeg) str = the string where you want to find the text (the column text you want to change) pos = position to fetch. You can specify the position for it to start searching for OPTIONAL
  • This command returns a numeric value.

So, for example, it would be an UPDATE more or less like this:

UPDATE tabela SET coluna = image.jpg WHERE LOCATE('.jpeg', coluna) > 0

If each image has a different name you can still use CHAR_LENGTH() and SUBSTRING_INDEX() to take only part of the name and put in the new name.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.