CODEWITHSUNDEEP
sqlsql-servert-sqlif-statement
lbrrrr
lbrrrr

Reputation: 75

IF ELSE in SQL Server

I have the following column in Microsoft SQL Server called Type and I want to create another column called Type 1 which would be 1 if account starts with letter A, 2 if account starts with D....

I am new with this so could someone please advise?

Type    Type 1  
AD  1  
AV  1  
AC  1  
DE  2  
DR  2  
DG  2  
KL  3  
KL  3

Upvotes: 0

Views: 149

Answers (6)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

If you wanted to number using the first letter of the 'type' column in ascending order, use the DENSE_RANK() in SQL Server.

  ;WITH cte_1
   AS
    ( SELECT Type
                    ,DENSE_RANK() OVER (ORDER BY LEFT(Type,1) ) [Type 1]
      FROM YourTable)
    SELECT *
     FROM cte_1
      WHERE [Type 1]=1 --here you can add your filter criteria

Upvotes: 0

ThatChris
ThatChris

Reputation: 752

I know this question has been answered already but I thought I would add my 2 cents.

If the amount of possible values is going to get very large, causing the case statement to become very large you may want to consider creating a loopkup table for yourself.

You would the be able to just do a join between the 2 tables to get the relevant column value.

Upvotes: 0

Eric
Eric

Reputation: 3257

If you want to put WHERE on computed column, you have to wrap it in subquery.

SELECT *
FROM (
    SELECT Type
        , (CASE LEFT(Type, 1)
            WHEN 'A' THEN 1
            WHEN 'D' THEN 2
        END) AS Type1
) a
WHERE Type1 = 'some condition'

Upvotes: 0

JosephStyons
JosephStyons

Reputation: 58685

As others have pointed out, a CASE statement will meet this need easily.

If you are going to be doing this translation often, consider adding a computed column to the table like so:

if object_id('ComputedColumnTest') is not null
  drop table ComputedColumnTest;
go

create table ComputedColumnTest
(
  TYPE varchar(2)
);
alter table ComputedColumnTest     --<<<<<<<
add TYPE1 AS                       --<<<<<<<
  (                                --<<<<<<<
  case when [TYPE] like 'A%' then 1--<<<<<<<
       when [TYPE] like 'D%' then 2--<<<<<<<
       else 0                      --<<<<<<<
  end                              --<<<<<<<
  )                                --<<<<<<<

insert into ComputedColumnTest([type]) values ('AD')
insert into ComputedColumnTest([type]) values ('AV')
insert into ComputedColumnTest([type]) values ('AC')
insert into ComputedColumnTest([type]) values ('DE')
insert into ComputedColumnTest([type]) values ('DR')
insert into ComputedColumnTest([type]) values ('DG')
insert into ComputedColumnTest([type]) values ('KL')
insert into ComputedColumnTest([type]) values ('KL')

select *
from ComputedColumnTest
where type1 = 2

Upvotes: 0

Ritesh Patel
Ritesh Patel

Reputation: 315

SELECT Type,
CASE WHEN SUBSTRING(Type, 1, 1)='A' THEN 1
     WHEN SUBSTRING(Type, 1, 1)='D' THEN 2
     WHEN SUBSTRING(Type, 1, 1)='K' THEN 3
     ELSE 0  END as 'Type 1'
FROM TableName

Upvotes: 0

D Stanley
D Stanley

Reputation: 152556

Use CASE instead of IF:

SELECT 
    Type,
    CASE 
        WHEN Type LIKE 'A%' then 1 
        WHEN Type Like 'D%' THEN 2 
        WHEN ...
    END AS Type1
...

Upvotes: 2

Related Questions