SQL returning multiple variables

Question

I am trying to write a query with a case statement. I am having problems outputting my case statement as multiple variables. Basically I want to take 1 column and out put the first part as outputcolumn1 "FIRST" and the last 4 characters as outputcolumn2 "LAST"

I tried doing some research but I am not finding anything to address my flavor... I know I am overlooking something really dumb.

Select *
Case When a.col_1 is NULL 
-- Grab all but last 4 as "FIRST"
Then LEFT(b.col_1, len(b.col_1) -4
-- Grab Last 4 as "LAST"
AND RIGHT(b.col_1, 4)
Else LEFT(a.col_1, len(a.col_1) -4
AND RIGHT(a.col_1, 4)

end as FIRST,LAST

From Table_1 as a
inner join Table_2 as b on a.TableID = b.TableID

McNets · Accepted Answer · 2017-02-23 00:15:12Z

5

First, there is a missing right parentheses here : LEFT(b.col_1, len(b.col_1) -4

Second, you cannot return two columns, use two case statements.

SELECT *,
       CASE WHEN a.col_1 IS NULL 
            THEN LEFT(b.col_1, LEN(b.col_1) -4) 
            ELSE LEFT(a.col_1, LEN(a.col_1) -4)
       END AS FIRST,
       CASE WHEN a.col_1 IS NULL 
            THEN RIGHT(b.col_1, 4) 
            ELSE RIGHT(a.col_1, 4)
       END AS LAST
FROM Table_1 AS a
INNER JOIN Table_2 AS b 
ON a.TableID = b.TableID

I would suggest you another syntax:

SELECT *,
       COALESCE(LEFT(a.col_1, LEN(a.col_1) - 4), LEFT(b.col_1, LEN(b.col_1) - 4)) AS FIRST,
       COALESCE(RIGHT(b.col_1, 4), RIGHT(a.col_1, 4)) AS LAST
FROM Table_1 AS a
INNER JOIN Table_2 AS b 
ON a.TableID = b.TableID

edited Feb 23, 2017 at 0:15

answered Feb 22, 2017 at 23:30

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

UPGRAYEDD Over a year ago

I started to nest the case statements like you have here but knew I was still missing something. Thanks!

McNets Over a year ago

I'm glad to be able to help.

Collectives™ on Stack Overflow

SQL returning multiple variables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related