List:General Discussion« Previous MessageNext Message »
From:Andy Bakun Date:December 10 2002 10:30pm
Subject:Re: Can I get the matching expression from REGEXP
View as plain text  
Look up SUBSTRING_INDEX in the mysql manual.

select SUBSTRING_INDEX(colX,' ',-1) from table

is what I think you want.  This will return everything after the first
space found.  May want to stick an if(...) construct in there for where
you don't want the rows without the spaces (see below).

Then something like:

update table set colY = SUBSTRING_INDEX(colX,' ',-1);

To satisfy your condition of the first two rows only (in your example):

select if(substring_index(colX, ' ', -1)) = colX,
	NULL, 
	substring_index(colX, ' ', -1)) as colY
from xx1 having colY is not NULL;

and for the update:

update table set colY = substring_index(colX, ' ', -1)
  where substring_index(colX, ' ', -1)) <> colX;

Andy.

On Tue, 2002-12-10 at 17:40, Mike Bosschaert wrote:
> Hi,
> In one of my tables I have a column which contains a combination of a string 
> (characters only), a space and a 1 or 2 letter combination. The string has no 
> fixed lenght. Like:
> 
>    abcd ef
>    bcdefgh i
>    etc
> 
> Now I want to remove the 1 or 2 letter combination from this column into 
> another column. I can select the rows with 
> 
> WHERE colX REGEXP ". .{1,2}$"
> 
> This wil return the first two rows only. But I cannot figure out how to get 
> the query to return the matching result (being ef and i).
> 
> Any help appreciated


Thread
Can I get the matching expression from REGEXPMike Bosschaert10 Dec
  • Re: Can I get the matching expression from REGEXPAndy Bakun10 Dec
  • Re: Can I get the matching expression from REGEXPRobert Citek11 Dec
    • Re: Can I get the matching expression from REGEXPMike Bosschaert11 Dec