concatenate 4 values in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

concatenate 4 values in query

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-06-25 : 02:26:53

I have a a table that have 4 different string values like this..

Attachment0
Attachment1
Attachment2
Attachment3

The table also have a ID that I would like to output

I would like to check if thoose values have a string value or if they have null values, if they have a null value I want to set it like this ISNULL(Attachment0,0) AS A0.
But if there is a string value I want A0 to be 1 instead. And finaly I would like to concatenate thoose values like this
@AStatus = (A0 + A1 + A2 + A3). Can I output both the ID and the @AStatus value

Regards

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-25 : 02:51:01

U can use case statement

Case when Isnull(Attachment0) then 0 else 1 end.

--------------------------------------------------
S.Ahamed

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-25 : 02:51:41

SELECT ID,
CASE WHEN Attachment0 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN Attachment1 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN Attachment2 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN Attachment3 IS NULL THEN 0 ELSE 1 END
FROM YourTable

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-06-25 : 03:26:11

Hi

That worked just great, Thanks a lot!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-25 : 03:50:46

CASE WHEN Attachment0 IS NULL THEN 0 ELSE 1 END
can be simplified to
ISNULL(Attachment0,0)

Madhivanan

Failing to plan is Planning to fail

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-25 : 20:53:35

quote:
Originally posted by madhivanan

CASE WHEN Attachment0 IS NULL THEN 0 ELSE 1 END
can be simplified to
ISNULL(Attachment0,0)

Madhivanan

Failing to plan is Planning to fail

Attacment0 is a string column.
I think yor are mistaking.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 01:22:27

Yes it is. Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources