?
Solved

Complex Update Statement...well, for me!

Posted on 2006-06-27
7
Medium Priority
?
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

764 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