Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

mysql subquery (query for function parameter)

Not sure if this is ok to combine so many things...

If I have a table with columns:
firstname
lastname

the name "experts exchange" in firstname.

I want to search for the space, and put everything after into `lastname`. ie(firstname:experts  lastname:exchange)

Logically, this is what I would want.
update table set `lastname` = substr(`firstname`, (SELECT instr(`firstname`, ' ') FROM table));

(even that though would include the space, so I would want instr + 1. Or I could do a second pass to remove the leading space afterwards....

Thanks in advance!
0
ststesting
Asked:
ststesting
2 Solutions
 
Cornelia YoderArtistCommented:
You could use SUBSTRING_INDEX(lastname, " ", -1) to find the last space.

So then you would select  SUBSTRING(lastname, SUBSTRING_INDEX(lastname," ",-1)+1) to get everything following the last space.
0
 
SharathData EngineerCommented:
You can try this.
Update test
Set lastname = trim(substring_index(firstname,' ',-1));

Open in new window


http://sqlfiddle.com/#!9/785c3/1
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now