CODEWITHSUNDEEP
mysqlsql
C.Programming
C.Programming

Reputation: 315

MySQL Replace string from one character to another

I am dealing with string paths with have an array index in them, indicated by [#] where # is the index. This index can be anything. What I am trying to do is to in my path string, remove all the [#] occurrences. So brackets and anything within brackets to be removed. I see the replace function but I am not sure how to use an arbitrary “middle” between the brackets to replace.

I want to remove from '[' to ']' and replace this with ''.

Ex path: Number[2].padding[1] reduced to Number.padding.

Right now I have something like :

replace(path, '[%]', '')

Where I'm trying to use % as a wildcard, but it is not functioning.

Upvotes: 1

Views: 339

Answers (1)

GMB
GMB

Reputation: 222702

If you are using MySQL 8.0, you can use REGEXP_REPLACE to capture parts of string like [#] as follows 

SELECT REGEXP_REPLACE(@txt, '\\[[^]]*\\]', '');

Regexp breakdown :

\\[      # opening square bracket
[^]]*    # 0 to N characters other than a closing square bracket 
\\]      # closing square bracket

Example with your test data :

SET @txt = 'Number[2].padding[1] reduced to Number.padding';
SELECT @txt input, REGEXP_REPLACE(@txt, '\\[[^]]*\\]', '') output

| input                                          | output                                   |
| ---------------------------------------------- | ---------------------------------------- |
| Number[2].padding[1] reduced to Number.padding | Number.padding reduced to Number.padding |

Upvotes: 2

Related Questions