June 29, 2009 at 2:34 pm
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!
June 29, 2009 at 2:41 pm
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
June 29, 2009 at 2:43 pm
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
June 29, 2009 at 3:15 pm
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
June 29, 2009 at 3:43 pm
And without the CASE thingy
SELECTZIP + COALESCE('-' + PlusFour, '') AS FullZIP
FROM[Address]
N 56°04'39.16"
E 12°55'05.25"
June 29, 2009 at 3:48 pm
Peso (6/29/2009)
And without the CASE thingySELECTZIP + 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]
June 29, 2009 at 3:56 pm
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
How to Post Data/Code to get the best Help How to Post Performance Problems
June 29, 2009 at 4:05 pm
Florian Reischl (6/29/2009)
SET FUN ONOr, 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... 😉
June 29, 2009 at 4:09 pm
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 🙂
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply