Covert text to int mssql

Question

1.00/5 (1 vote)

See more:

Hello, need to replace NULL values with text
Example If is null to write ...

Error is

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

I try CONCAT,

CONVERT(INT, CONVERT(VARCHAR(12), 'A'))

But ho have lucky
Thank you

What I have tried:

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

Posted 16-Jul-22 10:55am

Goran Bibic

Updated 16-Jul-22 21:48pm

Add a Solution

Comments

Afzaal Ahmad Zeeshan 16-Jul-22 17:01pm

Why are you trying this to an integer? You know that "A" is not a number. If the data type of the column is an integer, perhaps, try saving a numeric value.

Otherwise, I would recommend using another column to indicate something. Even better is to just return a different value for a null column.

If you are showing the text on the UI, maybe just check for the null value and print the text...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

0x01AA · Accepted Answer · 2022-07-16T21:48:00

Solution 1

This should help. Instead of 'A' place whatever you like to show:

SELECT COALESCE(CAST([prazno2] AS VARCHAR(30)), 'A') AS Rb FROM DBO.cte

Posted 16-Jul-22 21:48pm

0x01AA

Comments

Goran Bibic 17-Jul-22 3:52am

prazno2 is data type int and don't work...same error

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

0x01AA 17-Jul-22 3:58am

Strange, I tested it. CAST([prazno2] AS VARCHAR(30)) is to convert prazno2 to char.
With what you replaced 'A'?

Goran Bibic 17-Jul-22 4:10am

SELECT COALESCE(CAST([prazno2] AS VARCHAR(30)), 'A') AS Rb FROM DBO.cte

I try this, you write, I just copy and have same error

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

0x01AA 17-Jul-22 4:11am

This SQL

SELECT COALESCE(CAST([prazno2] AS CHAR(30)), CONCAT('A', [Id])) AS Rd,
     [prazno2],
     [drawing_name]FROM [CP].[dbo].[pieces]

results in

Rd	prazno2	drawing_name
10                            	10	D1234                                             
20                            	20	C1234                                             
A3	NULL	IdNull                                            
A4	NULL	NULL

with this data in pieces

id	drawing_name	prazno2
1	D1234                                             	10
2	C1234                                             	20
3	IdNull                                            	NULL
4	NULL	NULL

0x01AA 17-Jul-22 4:20am

Sorry for the count of edits. Had some trouble with format ;)

Goran Bibic 17-Jul-22 4:58am

Where is LETTER A on your result?

0x01AA 17-Jul-22 5:34am

[Edit]
No it was the correct result. The last section in the big comment above shows the content of the test table.

The resut is

Rd	prazno2	drawing_name
10                            	10	D1234                                             
20                            	20	C1234                                             
A3	NULL	IdNull                                            
A4	NULL	NULL

Goran Bibic 17-Jul-22 11:18am

Complete code dont work with error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'A' to data type int.
In this line

(CASE WHEN prazno2 IS NULL THEN CONVERT(INT, CONVERT(VARCHAR(12), 'A')) ELSE prazno2 END) as Rb

;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
(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

0x01AA 17-Jul-22 11:30am

No, no, no. Whenever you like to have 'prazno2' as a message with char(xyz) when it is NULL you need to convert that column to char(xyz) before.

0x01AA 17-Jul-22 11:40am

Try to follow these simple rules in case you need a verbal output of a int field which can be NULL
a.) Cast that field always first to VARCHAR(xyz) before you use it later
b.) COALESCE does usually simplify. This instead of using NULLIF, CASE, what else
I hope it helps

Richard Deeming 19-Jul-22 5:02am

CONVERT(INT, CONVERT(VARCHAR(12), 'A'))

Seriously, what INTEGER do you think you should get as the result of converting the letter A to an integer?!

Stop and think about what you're trying to do here.

Goran Bibic 19-Jul-22 5:13am

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'A' to data type int.

Ok, i find another way

0x01AA 17-Jul-22 5:39am

See column 'Rd' above, the last two records show 'A3' and 'A4'

0x01AA 17-Jul-22 8:19am

Hi Goran
Any success? I don't see a reason why it should not work on your system...
Tried clear all the caches, restart the computer, what ever? :-))

0x01AA 17-Jul-22 4:25am

Btw. I'm testing with MSSQL 2018

[Edit]
MSSQL 2014 works also for me