Remove characters from a field in Mysql

Asked

Viewed 1,780 times

4

I have a table where I have several codes, I would like to get all the codes that start with the following sequence 1170 and remove the same.

Example, the following code 11701287871 would be 1287871. That’s possible?

mysql database

  • I answered how to recover, but if you need to change, I add the UPDATE syntax, just warn (actually it’s just a UPDATE tabela SET campo = SUBSTR( campo, 5 ) WHERE SUBSTR( campo, 1, 4 ) = '1170'. Back up before ;)

1 answer

6


Basically this:

SELECT
    SUBSTR( campo, 5 ) AS restantes
FROM
    tabela
WHERE
    SUBSTR( campo, 1, 4 ) = '1170'

The function SUBSTR (or SUBSTRING ) has that syntax:

SUBSTR( valor, inicio [, quantidade] )

If you omit the amount, it takes until the end of the string.

Applied to your case:

SUBSTR( '11701287871', 5 ) = 1287871
             ^-- começa da 5 e vai até o fim, pois não especificamos tamanho

SUBSTR( '11701287871', 1, 4 ) = 1170
         ^-- começa da 1 e pega 4 caracteres

Handbook:

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring

  • 1

    Thanks @Bacco, that’s right!

Browser other questions tagged mysql database

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