Select query and ISNULL
-
Hi,
I have this table, Table1 {userid, name, address, ....., columnA, columnB}. I want to do a select on this table.
SELECT userid, name, address, columnA + ' : ' + columnB AS columnAB
FROM table1
I want to select the last two columns as one. But both the fields, columnA and columnB are nullable. The fields are of type varchar.
If columnA is NULL and columnB is 'abc', columnAB should be 'abc'. (Not ' : abc')
If columnA is NULL and columnB is NULL, columnAB should be 'None'.
If columnA is 'xyz' and columnB is 'abc', columnAB should be 'xyz : abc'.
I tried using ISNULL different ways, but I am not able to figure it out. Currently I am selecting columnA and columnB seperately and take care of nulls and colons in the front end. Its no big deal, just wondering if it can be done in sql.
Thanks
-
Probably best done on the front end, but the following should work:
SELECT userid
,[name]
,address
,CASE
WHEN columnA IS NULL AND columnB is NULL
THEN 'None'
WHEN columnA IS NULL
THEN columnB
WHEN columnB IS NULL
THEN columnA
ELSE columnA + ' : ' + columnB
END AS columnAB
FROM table1
-
Try this:
Select UserID,FName,LName,
isnull(Case when FName is Null then LName
when LName is Null then FName
Else FName + ':' + LName End,'None')
from Test_Name
-
Thank you. I think I will keep it in the front end.
-
WHY? The code won't be any simpler there!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You should ask that question to Ken
. -
Ok... I know... "put up or shut up, Jeff"... fine
Let's see you do it any simpler in an app 
SELECT COALESCE(A+':'+B, A, B, 'NONE')
FROM yourtable
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Not even gonna try... but I'm lazy and I know this works fine
. -
Sorry, Remi... that wasn't directed at you... I got so ticked
at the mere suggestion that such a simple data manipulation be done in the app that I even forgot to post a good solution. It's amazing that some people think so far out of the box, they forget how good the box they're in is. Senthil,
The method I posted to solve your problem will actually cut down on network traffic, as well... instead of your code having to return two values to the client and then have the client solve the problem, the method returns just one thing... the answer. Think about it if you needed to do this for hundreds of rows... doing it at the client effectively doubles the network traffic.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply