Solved

Complex Update Statement...well, for me!

Posted on 2006-06-27
7
250 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 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
Technology Partners: 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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 …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

696 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