MySQL Replace string from one character to another

Question

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.

I’m not sure which one you are pointing to. I know about replace and I’ve thought along the lines of a substring, but I can’t seem to find one that can specify from one character to another character — C.Programming
– C.Programming, Commented Feb 13, 2019 at 22:21

GMB · Accepted Answer · 2019-02-13 22:37:58Z

2

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 |

edited Feb 13, 2019 at 22:37

answered Feb 13, 2019 at 22:31

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

C.Programming Over a year ago

Great answer. Similar to what I figured out but a much better detailed answer with output. Also I didn't know about the carrot so that could've had consequences. Thank you!

C.Programming Over a year ago

it seems that I am not using MySQL 8.0 afterall. Is there a way to do this without REGEXP_REPLACE?

C.Programming Over a year ago

I am trying get the array brackets and text out of a path so I can compare to my parameter variable path.

GMB Over a year ago

@C.Programming ... without REGEXP_REPLACE, this will probably be painful... Question : is this always a one-digit number that is within the brackets, or it may be more than one digit ?

C.Programming Over a year ago

there may be more than one digit

|

Collectives™ on Stack Overflow

MySQL Replace string from one character to another

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related