2

I have a column PhoneNumber varchar(50) and i want to build an expression for a derived column PhoneNumberType varchar(50) based on below,

IF PhoneNumber <> NULL then
Set to ""Office""
Else
Set to NULL
END IF

I tried as 

!ISNULL(PhoneNumber ) ? "Office" : PhoneNumber

But i am getting an error while mapping the column as

Column PhoneNumberType can not convert between unicode and non-unicode string data types

Update : Can i just go to the advance editor of derived column component and select string [DT_STR] instead of auto generated , unicode string [DT_WSTR] data type in column properties ?

is this a good practice ?

Ajinkya Pujari
  • 71
  • 1
  • 6
  • Try this `!ISNULL(PhoneNumber ) ? (DT_WSTR)"Office" : PhoneNumber`, or if you want non-unicode then the other way around `!ISNULL(PhoneNumber ) ? "Office" : NULL(DT_STR, 100, 1252)` – EzLo Jun 01 '18 at 07:52
  • @EzLo Can i just go to the advance editor of derived column component and select string [DT_STR] instead of auto generated , unicode string [DT_WSTR] data type in column properties ? – Ajinkya Pujari Jun 01 '18 at 08:12
  • 1
    If you are creating a new column in the derived column, then the result type is infered from the result of the expression you put. When using the ternary operator ?, both ends must return the same data type. You error is probably popping up becase one result is on DT_STR and the other on DT_WSTR. On the other hand, if you are replacing a column in your derived column then the data type must remain the same. – EzLo Jun 01 '18 at 08:14

2 Answers2

0

You can use the following expression to generate a DT_STR column and it also check for empty strings:

ISNULL([PhoneNumber]) || TRIM(PhoneNumber) == "" ? NULL(DT_STR, 50, 1252) : (DT_STR,50,1252)"Office"
Hadi
  • 36,233
  • 13
  • 65
  • 124
0

Well, bellow is the answer i figured out , ISNULL() didn't work as per my requirement

PhoneNumber=="" || LEN(TRIM(PhoneNumber)) == 0 ? (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252) : (DT_STR,50,1252)"Office"
Ajinkya Pujari
  • 71
  • 1
  • 6
  • you can replace `PhoneNumber=="" || LEN(TRIM(PhoneNumber)) == 0` with `TRIM(PhoneNumber)==""`. also the `ISNULL` function is not working because you have empty strings (it is different from NULL), also i think that the best solution is `ISNULL([PhoneNumber]) || PhoneNumber=="" ` because you have to check for empty strings and NULL values – Yahfoufi Jun 05 '18 at 11:55
  • i edited the other answer to fit your requirements, because in you question you didn't mentioned that you are working with empty strings – Yahfoufi Jun 05 '18 at 12:41