Solved

SQL Syntax for combining fields

Posted on 2014-09-14
25
226 Views
Last Modified: 2014-09-14
Hello,

I have a table with multiple fields that I am trying to combine those fields into a single field.  Having some issues with the proper syntax.  Others have helped me to a certain point but when I then try to modify it does not work for me.

Looking for proper syntax to be able to do the following:

Fields:

Street
City
State
ZipCode

Am trying to combine these four fields together with proper punctuation and spacing.  If ANY of the fields are blank I would like to leave out the comma or space, etc...

I thought I had that portion of it working but then found that if the State field or ZipCode were missing it would not display correctly.

Looking to do the same thing with the FirstName MiddleName and LastName fields...combining into one field with proper punctuation and spacing, and that if ANY of the fields are blank that it will automatically adjust accordingly.

Hoping someone can provide me with the proper suggestion.

Here is the latest syntax that I have tried:

SELECT Trim([BusinessStreet] & IIf(Nz([BusinessStreet]) = "", " ",", ") & [BusinessCity] & IIf(Nz([BusinessCity]) = "", " ",", ") & [BusinessState] & IIf(Nz([BusinessState]) = "", " ",", ") & " " & [BusinessPostalCode] & IIf(Nz([BusinessPostalCode]) = "", " ",", ")) AS FullName, * INTO sample7f
FROM MasterContracts;

What am I missing???

Please help
0
Comment
Question by:FreddyBass
  • 11
  • 8
  • 6
25 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40321796
try

select trim(([FirstName] + " ") & ([MiddleName] + " ") & [LastName]) as FullName
from TableX
0
 

Author Comment

by:FreddyBass
ID: 40321811
Hi Rey,

Thank you for your response.

I used this syntax:

select trim(([First Name] + " ") & ([Middle Name] + " ") & [Last Name]) AS FullName, * INTO sample7h
from MasterContacts

When I run it...if the Middle Name field is an initial it does not add the proper punctuation.

That is the same trouble that I am still having when trying to combine the Street, City, State and zipcode fields.  If any one of the fields are blank I am trying to have the end result adjust for that by putting in the proper syntax.

Just have not been able to find the correct way yet.

Hoping someone can tell me what I am missing.
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 40321829
Try this:

SELECT
  (isnull([BusinessCity] + ', ', '') +
  isnull([BusinessState] + ', ', '') +
  isnull([BusinessPostalCode], ''))
   AS address,

  (isnull([FirstName] + ' ', '') +
  isnull([MiddleName] + ' ', '') +
  isnull([LastName], ''))
   AS FullName

FROM MasterContracts;

Here is a fiddle: http://sqlfiddle.com/#!3/8ec76/28
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 24

Expert Comment

by:mankowitz
ID: 40321832
Also, if MiddleName is always an initial, then you substitute
  isnull([MiddleName] + '. ', '') +

Open in new window

0
 

Author Comment

by:FreddyBass
ID: 40321845
Hello mankowitz,

Thank you for your reply but I get an error when I try to run.

Says "Wrong number of arguments used with function in query expression"

Here is what I tried:

SELECT
  (isnull([BusinessCity] + ', ', '') +
  isnull([BusinessState] + ', ', '') +
  isnull([BusinessPostalCode], ''))
   AS address, * INTO sample7aa
from MasterContracts;

Please advise...your assistance would be much appreciated

On the MiddleName field, sometimes it would be full name other times it could be initial only...Hope you can help
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 40321915
it worked in the fiddle. What version database are you using?
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 40321920
Here's a fiddle that puts a period for a one-letter middle name

SELECT
  (isnull([BusinessCity] + ', ', '') +
  isnull([BusinessState] + ', ', '') +
  isnull([BusinessPostalCode], ''))
   AS address,

  (isnull([FirstName] + ' ', '') +
   case when middlename is null then ''
   when len(middlename) = 1 then (middlename + '. ')
   else ([MiddleName] + ' ') end +
  isnull([LastName], ''))
   AS FullName

FROM MasterContracts;

See http://sqlfiddle.com/#!3/c8c8b/1
0
 

Author Comment

by:FreddyBass
ID: 40321925
I am using Access 2010

I go to Query --> Design --> add the table to the window --> Select SQL View and put in the code --> then try and ! (run the Query)

What am I missing?
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 40321934
ok, access has different syntax than sql server.. Try this


SELECT
  (iif(isnull([BusinessCity]), '', (BusinessCity + ', ')) +
   iif(isnull([BusinessState]), '', (BusinessState + ', ')) +
   iif(isnull([BusinessPostalCode]), '', (BusinessPostalCode)) )
   AS address
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 40321940
try this

SELECT Trim(([First Name] + " ") & IIF(Len([Middle Name])=1,  ([Middle Name] + ". "), ([Middle Name] + " ")) & [Last Name]) AS FullName, * INTO sample7h
 from MasterContacts
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 40321943
For the name, do this:
SELECT
  (iif(isnull([BusinessCity]), '', (BusinessCity + ', ')) +
   iif(isnull([BusinessState]), '', (BusinessState + ', ')) +
   iif(isnull([BusinessPostalCode]), '', (BusinessPostalCode)) )
   AS address,

   (iif(isnull([FirstName]), '', (FirstName + ' ')) +
iif(isnull(MiddleName), '', iif(len(middlename)=1, (middlename + '. '), (middlename + ' ') )) +
  iif(isnull([LastName]), '', (LastName)) )
   AS FullName
from MasterContacts
0
 

Author Comment

by:FreddyBass
ID: 40321948
Hi Rey,

Thank you for your reply,

The FirstName, MiddleName LastName worked.

How would I accomplish the same thing for BusinessStreet, City, State  ZipCode?

Based on the same assumption that any of the fields could be blank?

Looking forward...

Thank you for your input
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40321951
@freddy
what is the deal with the address? rules to follow?
0
 

Author Comment

by:FreddyBass
ID: 40321960
Hello Rey,

Here is the SQL syntax I am trying but it errors out

SELECT Trim(([BusinessStreet] + " ") & IIF(Len([BusinessCity])=1,  ([BusinessCity] + ". "), ([BusinessCity] + " ")) IIF(Len([BusinessState])=1,  ([BusinessState] + ". "), ([BusinessState] + " ")) & [BusinessPostalCode]) AS FullName, * INTO sample7i
 from MasterContacts

Comes back with a syntax error in query expression

Hope you can help
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40321963
post sample expected  formatted address...
0
 

Author Comment

by:FreddyBass
ID: 40321969
Hi Rey,

The fields that i am trying to combine are:

BusinessStreet
BusinessCity
BusinessState
BusinessPostalCode

Any one of these fields might be blank and contain no data so I am trying to find the proper syntax to adjust for this.

The BusinessStreet field could be a P.O Box or could contain an actual street address.

Examples of end result i am looking for:

P.O Box 355, Boston, MA  01235

410 Highland Ave, San Francisco, CA  99781

but if some fields were left blank...

P.O Box 355, MA  02135

or

Boston, 02135

or

410 Highland Ave, San Francisco,  99781

Thank you in advance for your input on this
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 40321977
SELECT
  (
   iif(isnull([BusinessStreet]), '', (BusinessStreet+ ', ')) +
   iif(isnull([BusinessCity]), '', (BusinessCity + ', ')) +
   iif(isnull([BusinessState]), '', (BusinessState + '  ')) +
   iif(isnull([BusinessPostalCode]), '', (BusinessPostalCode)) )
   AS address,

   (iif(isnull([FirstName]), '', (FirstName + ' ')) +
iif(isnull(MiddleName), '', iif(len(middlename)=1, (middlename + '. '), (middlename + ' ') )) +
  iif(isnull([LastName]), '', (LastName)) )
   AS FullName
from MasterContacts
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40321981
try this


IIf([BusinessStreet] & ""="","",[BusinessStreet] & ", ") & IIf([BusinessCity] & ""="","",[BusinessCity] & ", ") & ([BusinessState] + " " ) & ([BusinessPostalCode] + " " )
0
 

Author Comment

by:FreddyBass
ID: 40322026
Hi Rey,

That works pretty well.

Not sure if I am being to picky?

Is it possible to make it so that regardless of the field that does not contain data is blank that the spacing and punctuation is adjusted properly?

Example:

If the BusinessStreet is blank and the BusinessState is blank then the address appears as:

Boston, 02135

Please tell me if I am asking to much?

I really do appreciate all the assistance you have provided to me.
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 40322030
my solution should render that way.
0
 

Author Comment

by:FreddyBass
ID: 40322052
Hi mankowitz,

Thank you for your input, but

When I go to Access 2010 --> Go to Design Query --> Add the table I want to work with --> then Go to SQL View

I paste the following code in the window

SELECT
  (
   iif(isnull([BusinessStreet]), '', (BusinessStreet+ ', ')) +
   iif(isnull([BusinessCity]), '', (BusinessCity + ', ')) +
   iif(isnull([BusinessState]), '', (BusinessState + '  ')) +
   iif(isnull([BusinessPostalCode]), '', (BusinessPostalCode)) )
   AS addressss,

   (iif(isnull([First Name]), '', (First Name + ' ')) +
iif(isnull(Middle Name), '', iif(len(Middle Name)=1, (Middle Name + '. '), (Middle Name + ' ') )) +
  iif(isnull([Last Name]), '', (Last Name)) )
   AS FullNamesss
from MasterContacts

When I run the command
I get the following error

Please see the file I attached
syntax-error.png
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40322058
<If the BusinessStreet is blank and the BusinessState is blank then the address appears as:

 Boston, 02135
>

the query is already doing that..
0
 

Author Comment

by:FreddyBass
ID: 40322086
Thank you Rey,

I have performed the testing and the results are what I am trying to achieve.  There are a couple of records that have PO Box in the BusinessStreet field that are only displaying the PO Box in the complete field.  Yet when I look back in the records for that there is info in the rest of the fields.  When I go back to the table and change other fields for BusinessStreet and then run it they appear for those changed records.  Very strange
0
 

Author Comment

by:FreddyBass
ID: 40322091
I have gone back to those records and changed the information in the table.  Regaurdless of what I change it to the only thing that will display is the BusinessStreet.  I have even tried changing everything in the record, all of the fields and still the same result
0
 

Author Closing Comment

by:FreddyBass
ID: 40322111
Rey was able to provide me with the proper syntax to achieve my goals for combining.  I would like to thank him for his continued effort in assisting me with this.

Would also like to thank mankowitz for trying to help
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

792 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