Click here to Skip to main content
15,566,394 members
Please Sign up or sign in to vote.
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
Updated 16-Jul-22 22:48pm
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

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
 
Share this answer
 
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 ;)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900