Solved

Complex Update Statement...well, for me!

Posted on 2006-06-27
7
245 Views
Last Modified: 2012-06-27
Ok, here's what I'm trying to figure out.  I was told that what I want to do CAN be done, but it's just a matter of knowing HOW to do it.  Please understand that I'm a novice DBA, but can pick up QUICKLY.  (Certified in Oracle 8i, passed SQL Server Administration)

We have a first name column, middle initial column and last name column.  We need to take out (actually hide) the middle initial field on the database interface, but move all those middle initials to the first name.  See example below:

This is how it is now in SQL Server:                                                                  This is how the database interface looks:

first_name                    middle_initial                 last_name                                First Name: John        Middle Name: H.
------------                    -----------------               -------------                              Last Name: Doe
John                             H.                                 Doe




We need it to look like this:                                                                  This is how the database interface looks:

first_name                    middle_initial                 last_name                                First Name: John H.      
------------                    -----------------               -------------                              Last Name: Doe
John H.                          H.                                 Doe


I need to copy all the middle initials to the end of the first name.  Alot of the middle initials are either blank or NULL...I want to ignore those.

Can someone help me with the update code?

0
Comment
Question by:llputney
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Select first_name + IsNull(' ' + middle_initial, '') first_name
From ...
0
 
LVL 12

Accepted Solution

by:
wstuph earned 500 total points
Comment Utility
update YourTable
set first_name = ltrim(rtrim((first_name)) + ' ' + ltrim(rtrim(isnull(middle_initial, '')))

I think that should do it.  I'd try with just one entry first and see if it comes out how you want it.
0
 

Author Comment

by:llputney
Comment Utility

The following statement worked:

update YourTable
set first_name = ltrim(rtrim((first_name)) + ' ' + ltrim(rtrim(isnull(middle_initial, '')))


HOWEVER, I'm having trouble looking up records I've just updated.

select *
from tbl
where first_name = 'John' and last_name = 'Doe'

Didnt bring back results and I've even tried using the wildcard character like 'John%'....how can I search names without knowing the middle initial?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 27

Expert Comment

by:ptjcb
Comment Utility
Something like:
select *
from tbl
where substring(first_name, 1, 4) = 'John' and last_name = 'Doe'
0
 

Author Comment

by:llputney
Comment Utility
Can you tell me what 1 and 4 meant?  Please bear with me, I am a novice.
0
 
LVL 27

Expert Comment

by:ptjcb
Comment Utility
No problem - start at the 1 character and the substring is 4 characters long.  
SUBSTRING ( expression ,start , length )
 
0
 
LVL 12

Expert Comment

by:wstuph
Comment Utility
how are you using the wildcard?  

select * from yourtable where first_name like 'john%' should bring back all Johns - can you post the exact wildcard query?
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

7 Experts available now in Live!

Get 1:1 Help Now