I want to concatenate the name of the product with "-v2" if the id is an odd number

CREATE TABLE PRODUSE (
IdProdus int PRIMARY KEY IDENTITY,
Denumire varchar(36) NOT NULL,
IdCateg int NOT NULL
)
GO

SELECT IdProdus, Denumire,IIF(IdProdus%2=1, concat(Denumire, '-v2'), 0)
from PRODUSE;

The error is like

Conversion failed when converting the varchar value 'Cablu USB-USB, 0.5m-v2' to data type int.

'Cablu USB-USB, 0.5m' is the first name in the table.

CodePudding user response：

Your inline IF is returning two different data types - it's not clear what your intentions are but either way you should not be returning 0, try either a blank string or the unmodified column:

Iif(IdProdus % 2 = 1, concat(Denumire, '-v2'), Denumire)

CodePudding user response：

That's because if your IIF condition is not satisied, in that case you are returning 0. In that case you should return the column value.

Here is the example -

SELECT IdProdus, Denumire,IIF(IdProdus%2=1, concat(Denumire, '-v2'), Denumire)
from PRODUSE;

CodePudding user response：

• number % 2, if it produces anything except 0 then it is an odd number
• concat function to join the Denumire to the string -v2

    select
    *,
    case when IdProdus % 2 = 1 then concat(Denumire, "-v2") else Denumire end as new_name
    from PRODUSE

if your SQL supports IFF() function then you can also use this:

    select
    *,
    IFF(IdProdus % 2 = 1, concat(Denumire, "-v2"), Denumire) as new_name
    from PRODUSE