?
Solved

Complex Update Statement...well, for me!

Posted on 2006-06-27
7
Medium Priority
?
255 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.
Suggested Courses

850 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