Solved

mysql subquery (query for function parameter)

Posted on 2014-01-10
2
256 Views
Last Modified: 2014-02-11
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
Comment
Question by:ststesting
2 Comments
 
LVL 27

Accepted Solution

by:
yodercm earned 250 total points
ID: 39772649
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
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 39773089
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now