Covert text to int mssql

Goran Bibic

4y976SQL

Hello, need to replace NULL values with text (letter A or ... or N/A)
Example If is null to write ...

Error is

Conversion failed when converting the varchar value 'A1' to data type int.

SELECT (CASE WHEN prazno2 IS NULL THEN CONCAT('A',id) ELSE prazno2 END) as Rb FROM DBO.cte

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Goran Bibic4y ago
COMPLETE CODE

LINE 17 AND 18
WHEN PUT INSTEAD NUMBER DONT WORK
;With cte1 as
(
    SELECT p.redni_broj, p.sifra_crteza,(n.redni_broj) as id, n.sifra, p.opis_sifre, n.naziv, null as prazno,null as prazno2
    from registracija_narudzbe_stavke p
    inner join registracija_narudzbe_materijal n on p.sifra_crteza = n.crtez
), cte2 as
(
    select redni_broj, id, sifra, sifra_crteza, opis_sifre, naziv, prazno, prazno2
    from cte1
    union
    select p.redni_broj, null as nr, null as Material, p.sifra_crteza,  opis_sifre, null as naziv, null as prazno,
    NULLIF(p.redni_broj,0) as prazno2
    from registracija_narudzbe_stavke p
)
select
    -- NULLIF(prazno2,'3 ') as Rb
     (CASE WHEN prazno2 IS NULL THEN CONVERT(INT, CONVERT(VARCHAR(12), 'A')) ELSE prazno2 END) as Rb
    ,(CASE WHEN CAST(id AS varchar(255)) IS NULL THEN '99' ELSE id END) as RB2
    ,isnull(sifra, sifra_crteza) Sifra
    , isnull(naziv,opis_sifre) Opis
from cte2
order by redni_broj
,case when prazno2=0 then 4 else 6 end
Sachin Singh4y ago
Then you need to share your CTE, CTE has some issue.
Goran Bibic4y ago
https://www.c-sharpcorner.com/members/sachinsingh
SELECT IsNull(Cast(prazno2 as Varchar(50)),'N/A') as Rb FROM DBO.cte
Error

Conversion failed when converting the varchar value 'N/A' to data type int.
Sachin Singh4y ago
This means your prazno2
column is of int type

you can do this
SELECT IsNull(Cast(prazno2 as Varchar(50)),'N/A')
as Rb FROM DBO.cte
Goran Bibic4y ago
https://www.c-sharpcorner.com/members/sachinsingh

Conversion failed when converting the varchar value 'N/A' to data type int.
Sachin Singh4y ago
1. SELECT IsNull(prazno2,'N/A')
2. as Rb FROM DBO.cte
or
SELECT (CASE WHEN prazno2 IS NULL THEN
CONCAT('A',Cast(id as varchar(16))
ELSE prazno2 END
) as Rb
FROM
DBO.cte

Jaydeep Patil4y ago

use some casting mechanism something like this

 cast(product_id AS UNSIGNED)

https://stackoverflow.com/questions/19702967/casting-varchar-to-int-in-mysql