How Do I Update Following Select Back To Table_A.Lastname

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

This is my select code

select

SQL

Table_A.MEMBER_ID, left(Table_B.NAME, charindex(' ', Table_B.NAME)-1) AS 'Last_Name', Table_B.NAME, charindex(' ', Table_B.NAME) as 'Index'
from Table_B, Table_A
where Table_B.SEQ_NO = Table_A.SEQ_NO and Table_B.RACE_CD = 'C' and Table_A.LName is NULL and charindex(' ', Table_B.NAME) >= 1

I want the last name split from full name and update back to Table_A.LName if is empty.
Thanks.

Posted 10-Mar-15 5:27am

Member 11513539

Updated 10-Mar-15 17:03pm

v2

Add a Solution

Comments

Member 11513539 10-Mar-15 12:29pm

This just one time activity to split full name from existing table and update to last name which user didn't key-in since day one.

[no name] 10-Mar-15 13:26pm

Alright - I updated my solution. I think it will solve your problem. Please accept it if it does.
Please note that I didn't get a notification of your comment because you commented on your own question. You should comment on a proposed solution to let the author of it be notified (just as a hint for the future).

Member 11513539 10-Mar-15 23:05pm

Ok, noted. thx. :)

3 solutions

Solution 3

SQL

select    case when (len(table_a.fullname)-len(replace(table_a.fullname,' ','')))>0 then reverse(substring(reverse(table_a.fullname),0,CHARINDEX(' ', reverse(table_a.fullname)))) else table_a.fullname end as [Last Name]
from table_a, table_b
where table_a.id = table_b.id
and table_b.region = 'C'

Posted 10-Mar-15 7:07am

Abdulnazark

Solution 4

I completely agree with manchanx in Solution 1, but to answer the question as asked ...

You can use a CASE clause - see A Simple Use of SQL CASE Expression[^]. For example:

SQL

select case when CHARINDEX(' ', table_a.fullname) = 0 then table_a.fullname ELSE
        left(table_a.fullname, charindex(' ', table_a.fullname) - 1) END as [Last Name]
FROM table_a
INNER JOIN table_b ON table_a.id = table_b.id
WHERE table_b.region = 'C'

You may also need to consider a situation where fullname consists of forename+middlename+surname - in which case you will need to find the last space not the first one. A neat trick to use is the REVERSE[^] function.

SQL

select case when CHARINDEX(' ', table_a.fullname) = 0 then table_a.fullnameName ELSE
        REVERSE(SUBSTRING(REVERSE(table_a.fullname),1,  CHARINDEX(' ', REVERSE(table_a.fullname)))) END as [Last Name]
FROM table_a
INNER JOIN table_b ON table_a.id = table_b.id
WHERE table_b.region = 'C'

As you can see it's starting to get a bit messy - so I refer you to Solution 1

Posted 10-Mar-15 7:19am

CHill60

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

manchanx · Accepted Answer · 2015-03-10T05:43:00

I suggest you reconsider your Database Model. Doing such string-operations on a regular basis in SQL-Statements yields bad performance and bad maintainability. You could simply add another column that contains exactly that part of the name you want to get here. You would only have to "calculate" it once (in your application or in a stored procedure) and that would be much simpler than squeezing it into a select statement. Also please take a look at "Joins": Joins are usually the best way to accomplish what you're doing here with where table_a.id = table_b.id.

Or, alternatively: Just select the column fullname as it is and the column region and do the string operations whereever you do what you're doing now after this query.

edit:

SQL

select
  fullname,
  region,
  lastname =
    case
      when fullname like '% %' and region = 'C' then
        left(fullname, charindex(' ', fullname) - 1)
      when fullname like '% %' and region <> 'C' then
        reverse(left(reverse(fullname), charindex(' ', reverse(fullname)) - 1))
      else fullname
    end
from TableA A
join TableB B on A.id = B.id