MySQL: Formatting a string

Question

My database contains a string pattern that is used to allow for easy user editing via a JS-script.

The string is basicly formatted like so:

aaa[bbb#ccc]ddd[eee#fff]ggg

the result I am looking for is

aaacccdddfffggg

I'd like to do this when selecting the string from the database. I'm guessing a regex should do the trick. But my knowledge in the subject of regex's is rather limited. However, this is not a requirement, if there exist a more elegant solution to the problem.

Konerak · Accepted Answer · 2010-12-09 08:11:12Z

1

Unfortunately, you can only use a MySQL REGEXP in a WHERE clause, to match against values. You can't use them to transform Strings.

You'll either need to do it clientside, or work with the other String Functions. A MID() would do the trick, if the lengths and positions of the substrings are fixed. If not, use POSITION() (or LOCATE()) to find the special characters []#.

answered Dec 9, 2010 at 8:11

Konerak

39.8k13 gold badges102 silver badges121 bronze badges

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

2 Comments

dotmartin Over a year ago

Hehe, yeah! I see your point. I was not thinking straight. Regex's are not in any way related. My bad. :) I'll give it a try using POSITION().

Konerak Over a year ago

@dotmartin: no actually regexes would be the way to go if the format is jumpy - any decent programmer would think about using those, so you were thinking straight :). It's just that MySQL can only use them to match, so you'd need a clientside regexp in your Perl/PHP/Rails/... code.

Collectives™ on Stack Overflow

MySQL: Formatting a string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related