Concatenating fields with possible additional character

Question

Post reply

Concatenating fields with possible additional character

lk-681111

SSCrazy

Points: 2915
More actions
June 29, 2009 at 2:34 pm

#207691

Hello:
I need to concatenate a number of fields in my SQL Server database for export to an external database. Two of the fields I need to concatenate are ZIP and PlusFour. I can do this:
SELECT (ZIP+'-'+PlusFour) AS FullZIP
FROM Address
...but if there is no value in the PlusFour field, I don't want the dash to be added. I'm sure there's an easy way to make the dash conditional...right? 🙂
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not knowing the possible values in PlusFour, try this:

SELECT (ZIP + case when PlusFour is not null then '-' + PlusFour else '' end) AS FullZIP

FROM Address

John Rowan SSC Guru Points: 56440 More actions · Answer 2

DECLARE @Address TABLE(Zip varchar(10), PlusFour varchar(4))

INSERT INTO @Address

SELECT '68114', NULL UNION ALL

SELECT '68132', '1234'

SELECT ZIP + CASE WHEN LEN(PlusFour) > 0 THEN '-'+PlusFour ELSE '' END AS FullZIP

FROM @Address

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

lk-681111 SSCrazy Points: 2915 More actions · Answer 3

Thanks to you both! Just what I needed--I was close, but I was doing CASE WHERE instead of CASE WHEN. D'oh!

Thanks again!:-D

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

And without the CASE thingy

SELECTZIP + COALESCE('-' + PlusFour, '') AS FullZIP

FROM[Address]

N 56°04'39.16"
E 12°55'05.25"

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Peso (6/29/2009)
And without the CASE thingy
SELECTZIP + COALESCE('-' + PlusFour, '') AS FullZIP
FROM[Address]

Or, if you aren't concerned about using only Standard SQL:

SELECT ZIP + isnull('-' + PlusFour, '') AS FullZIP

FROM [Address]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

SET FUN ON

Or, a UNION ALL if you generally don't like functions in your SELECT clause.

SELECT ZIP + isnull('-' + PlusFour, '') AS FullZIP

FROM [Address]

WHERE PlusFour IS NOT NULL

UNION ALL

SELECT ZIP AS FullZIP

FROM [Address]

WHERE PlusFour IS NULL

SET FUN OFF

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Florian Reischl (6/29/2009)
SET FUN ON
Or, a UNION ALL if you generally don't like functions in your SELECT clause.
SELECT ZIP + isnull('-' + PlusFour, '') AS FullZIP
FROM [Address]
WHERE PlusFour IS NOT NULL
UNION ALL
SELECT ZIP AS FullZIP
FROM [Address]
WHERE PlusFour IS NULL
SET FUN OFF

Umm, there is still a function in the first part of the query... 😉

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

Ouch...

ROLLBACK TO PREV_FUN

ALTER SNIPPET WITH (

SELECT ZIP + '-' + PlusFour AS FullZIP

FROM [Address]

WHERE PlusFour IS NOT NULL

UNION ALL

SELECT ZIP AS FullZIP

FROM [Address]

WHERE PlusFour IS NULL

)

RECONFIGURE;

Thanks Lynn!

Time for bed... See you 🙂