Remove characters from a field in MySQL

Question

Remove characters from a field in MySQL

Navigation

#1 by (6 votes)

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 it.

For example, the following code 11701287871 would be 1287871 . Is this possible?

mysql

asked by anonymous 15.12.2016 / 20:51

1 answer

One character length (ASCII vs. other encodings) in bytes Meaning of the term "Fluid Layout"

score 6 · Accepted Answer

Basically this:

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

The function SUBSTR (or SUBSTRING ) has this syntax:

SUBSTR( valor, inicio [, quantidade] )

If the quantity is omitted, it picks up 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

Manual:

link