I am aware of the existence of the RLIKE
and REGEX
operators, but it seems like they cannot be used for that.
Is there a function or an operator that would help me achieve splitting a text field and selecting it as two or more separate fields:
SELECT $1 as `field_a`, $2 as `field_b` FROM `table` WHERE `field` RLIKE '^(.+):开发者_高级运维(.+)$';
I am writing a log analyzer so it would be very handy to do that in SQL without additional text-crunching.
So you just want to split the string on the first occurrence of ":"?
There are several ways to achieve this in MySQL.
Using your example, here are two approaches off the top of my head. Hopefully they are helpful to you:
select substr(`field`,1,instr(`field`,':')-1) as `field_a`,
substr(`field`,instr(`field`,':')+1) as `field_b`
FROM `table`
WHERE `field` RLIKE '^(.+):(.+)$';
select left(`field`,instr(`field`,':')-1) as `field_a`,
right(`field`,length(`field`)-instr(`field`,':')) as `field_b`
FROM `table`
WHERE `field` RLIKE '^(.+):(.+)$';
精彩评论