Solved

SQL Syntax for combining fields

Posted on 2014-09-14
25
222 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 119

Expert Comment

by:Rey Obrero
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
 
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 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

11 Experts available now in Live!

Get 1:1 Help Now