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
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
