How to remove keywords between { } keys in the Mysql field?

Asked

Viewed 95 times

2

I have a table that contains a field with some values between keys, how do I delete the chaves and the valores that are within them?

Example:

 cod | Movimento            |

 01  | Prazos {aguardando}  |

Prazos {aguardando} should come this: Prazos. That is, remove {conteudo}

I want to delete these keys and the characters inside it. How do I do this?

mysql sql replace

1 answer

3

Do:

SELECT LOCATE('{', SEUCAMPO), LOCATE('}', SEUCAMPO)
FROM SUATABELA;


SELECT SUBSTRING(SEUCAMPO,LOCATE('{', SEUCAMPO), LOCATE('}', SEUCAMPO))
FROM SUATABELA;


SELECT REPLACE(SEUCAMPO,SUBSTRING(SEUCAMPO,LOCATE('{', SEUCAMPO), LOCATE('}', SEUCAMPO)),'')
FROM SUATABELA;

Sqlfiddle

There is no need to 1º and 2º SELECT, is just a demonstration of how each function will return its value. I have separated the Fiddle so that you can better understand how the whole process works, explaining:

  • Find the position of { and } using the function LOCATE.
  • Through the position use SUBSTRING to extract the part you want to remove.
  • Now just use the function REPLACE, replacing by ''.

Note: For this solution to work in MSSQL just change the function LOCATE for CHARINDEX, both are equivalent.

Browser other questions tagged mysql sql replace

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