Concatenating Addresses

Question

Post reply

Concatenating Addresses

Ramona Roman

Old Hand

Points: 399
More actions
July 28, 2004 at 10:25 am

#85933

I am having trouble concatenating an address into one column for my query. The address is split up over several columns in the “Address” table as so:

StreetNumber    Street Direction StreetName       Thoroughfare                 StreetSuffix      Building               City                 State    Zip
===============================================================================
3375                  N                     SPRING HILL DR                   SE                    APT 1713                   SMYRNA       GA       30080
98475                S                      MORTIMER     DR                   NE                   APT2C             ATLANTA        GA       30855

I would like for that address to look like this in a single column: 3375 N SPRINGHILL DR SE APT 1713, SMYRNA, GA ostalCode>30080ostalCode>

I keep getting the following error message:

Syntax error converting the nvarchar value '5560           EPONCE-DE-LEON          ' to a column of data type int.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

The problem is that Street Number and ZIP are probably INTEGERS (INT).

When you use the + with INTs, that's not CONCATINATE. It means ADD.

So you need to CONVERT or CAST the Street Number and ZIP to VARCHAR or

someother character datatype and then concatenate.

For example: SELECT (CAST(Street_Number AS VARCHAR(5))) + Street_Direction + Street_Name

Test it.

-SQLBill

hoo-t SSChampion Points: 12713 More actions · Answer 2

This will happen if you try to concatenate a string which is all numeric (even if its defined as char or varchar) to character data. You will need to CAST it as char or varchar. Your Zip is a good example:

... + City + ', ' + State + ' ' + CAST(Zip as CHAR(5))

Steve

Ramona Roman Old Hand Points: 399 More actions · Answer 3

how do i account for columns that are "null"?

There is not always a "streetsuffix", for example.

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Use the ISNULL command.

SELECT ISNULL(streetsuffix, '')

that will replace any NULL streetsuffix with no space.

You will have to include the ISNULL with all columns SELECTed. (Unless they can't/don't have a NULL).

-SQLBill

Ramona Roman Old Hand Points: 399 More actions · Answer 5

For some reason, i get the following error message:

The isnull function requires 2 arguments.

hoo-t SSChampion Points: 12713 More actions · Answer 6

SELECT ISNULL(streetsuffix, '')

The first argument is the column name that might be null, (streetsuffix in SQLBill's example).

The second argument is what you want to replace the null with, ('' in SQLBill's example), can be a space, two adjacent single quotes (replaces the null with no space, as SQLBill explained, kind of like a null value in itself), or anything else you want to put in there.

Steve

Ramona Roman Old Hand Points: 399 More actions · Answer 7

ok...here's another question...sorry.

How would i use the CAST with the ISNULL?

SELECT ISNULL(CAST (streetnumber AS VARCHAR(5), '') ?

getanothername SSC Veteran Points: 249 More actions · Answer 8

getanothername

SSC Veteran

Points: 249

July 28, 2004 at 12:20 pm

#516642

select isnull(cast(streetnumber as varchar(5)),'')

Iain Parker SSC-Addicted Points: 409 More actions · Answer 9

To save putting isnull around all your feld names you might want to look at SET CONCAT_NULL_YIELDS_NULL.

Regards,Iain

Peter Kryszak SSCarpal Tunnel Points: 4368 More actions · Answer 10

That setting also helps set up a way to avoid extra spaces in the address where null fields occur.

Instead of adding spaces between the address parts outside of the ISNULL(CAST(... concatenate the space in first argument of the ISNULL and you won't get the extra spaces. Like so:

isnull(cast(streetnumber as varchar(5))+' ','')

That's especially important for fields like direction that are usually optional.

Also, do yourself a favor and put all this into a user defined function for easy reuse. It can take all the fields as arguments or just an unique key to the record with the address and find the parts there.

AddressString( number, prefix, street, suffix ) or AddressString( streetId )