Solved

Complex Update Statement...well, for me!

Posted on 2006-06-27
7
247 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
ID: 16994575
Select first_name + IsNull(' ' + middle_initial, '') first_name
From ...
0
 
LVL 12

Accepted Solution

by:
wstuph earned 500 total points
ID: 16994587
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
ID: 16994946

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 27

Expert Comment

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

Author Comment

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

Expert Comment

by:ptjcb
ID: 16995222
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
ID: 16995355
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 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

18 Experts available now in Live!

Get 1:1 Help Now