How to remove words between braces {} in the field in Mysql?

Question

How to remove words between braces {} in the field in Mysql?

Navigation

#1 by (3 votes)

2

I have a table that contains a field with some values in braces, how do I exclude the chaves and the valores that are inside 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

asked by anonymous 20.11.2017 / 11:39

1 answer

Remove duplicate values from an array - php Insert character at last cursor position within input

score 3 · Answer 1

Make:

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

You do not need 1º and 2º SELECT , it's just a demonstration of how each function will return its value. I've separated Fiddle so you can better understand how the whole process works, explaining:

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

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