locked
Computed Column - based on IF or CASE RRS feed

  • Question

  • Hi there,

    I have a table which has a column say "Code" (type nchar(10)).

    I need a column which is computed automatically based on the value in Column "Code". Let say this column is called "Flag".

    The condition to be applied is:

    IF length of "Code" is > 0, then Flag should be equal to 1, else it should be equal to 0.

    Given that Code can have extra spaces before and after, I will prefer to TRIM the code value first and then calculate the LEN.

    Can someone please advise on how can I achieve this? I wish to have this computed for each row in the table when inserted or updated. I don't wish to run the "alter table" command as a transact-sql.

    thanks,
    JT

    Wednesday, January 27, 2016 6:31 PM

Answers

  • Correction to Andy's expression see in this example (just change your expression for computed column to the one I used):

    declare @t table (code nchar(10))
    insert into @t (code)
    values ('   a  '), ('b'), ('c'), (null), ('       ')
    
    select code, len(code) as Len, len(LTRIM(code)) as TrimmedLen, case when code is not null and LEN(LTRIM(code)) > 0 then 1 else 0 end as flag
       from @t


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, January 27, 2016 7:26 PM
  • I never made computed column a primary key, but assuming it's possible, all you need to do is to add

    NewColumn  as COALESCE(my current expression, space(10))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, January 28, 2016 3:55 PM

All replies

  • Try  this:

    CREATE TABLE dbo.Products 
    (
        ProductID int IDENTITY (1,1) NOT NULL
      , QtyAvailable smallint
      , UnitPrice money
      ,Code varchar (32)
      , InventoryValue AS CASE WHEN LEN(Code) > 0 Then 1 ELSE 0 END
    );



    Andy Tauber
    Data Architect
    The Vancouver Clinic
    Website | LinkedIn

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Naomi N Wednesday, January 27, 2016 7:21 PM
    • Marked as answer by JT_Singh Thursday, January 28, 2016 11:31 AM
    • Unmarked as answer by JT_Singh Thursday, January 28, 2016 11:50 AM
    Wednesday, January 27, 2016 6:39 PM
  • Correction to Andy's expression see in this example (just change your expression for computed column to the one I used):

    declare @t table (code nchar(10))
    insert into @t (code)
    values ('   a  '), ('b'), ('c'), (null), ('       ')
    
    select code, len(code) as Len, len(LTRIM(code)) as TrimmedLen, case when code is not null and LEN(LTRIM(code)) > 0 then 1 else 0 end as flag
       from @t


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, January 27, 2016 7:26 PM
  • Thanks folks. This is helpful.

    Can you let me know how can I add multiple If conditions in the above situation for computed column? I can't use CASE as the condition is not based on a single column value.

    For e.g. consider the following three input columns:

    Code1 - char (10)

    Code2 - char (10)

    Type - char (10)

    I need to compute the new column with the following logic:

    If Len(Code1)>0 THEN new column = Code1

    Else

    If Len(Code2)>0 THEN new column = Code2

    Else

    new column = Type

    Many thanks - JT


    JT

    Thursday, January 28, 2016 11:41 AM
  • It will be similar to what already posted, e.g.

    ComputedColumn as

    case when LEN(Code1) > 0 then Code1 when LEN(Code2) > 0 then Code2 else Type END



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, January 28, 2016 1:29 PM
  • Many thanks. 

    But I have hit another roadblock

    I'm trying to define this computed column as the Primary Key, but I'm getting an error "Cannot define PRIMARY KEY constraint on nullable column in table".

    This is because Code 1, Code 2 and Type can be NULL, but there will never be a situation when all the three are NULL. One of them will always have a value.

    Is there anyway I can get define the computed column as a primary key with the above in mind?


    JT

    Thursday, January 28, 2016 2:19 PM
  • I never made computed column a primary key, but assuming it's possible, all you need to do is to add

    NewColumn  as COALESCE(my current expression, space(10))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, January 28, 2016 3:55 PM