Click here to Skip to main content
15,942,757 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
Hi All

Please can anyone check this query once and let me know where I went wrong.
I am getting this error for select statement.

SQL
select (BuildingNumber +' '+ BuildingName +' '+ Postcode) as CompleteAddress from addressRecord
where( BuildingNumber + ' ' + BuildingName + ' ' + Postcode )like '%PL1 1AB%'


SQL
error:

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric

SQL
Datatypes:

BuildingNumber: numeric(18,0)
BuildingName: navarchar(50)
PostCode nvarchar(10)


Please can anyone help me.


Many Thanks
Posted

1 solution

Because your concatenated string starts with a numeric value, SQL assumes you mean to generate a number: so it is trying to convert ' ' to a number, and failing.

Try:
SQL
select (CAST(BuildingNumber AS NVARCHAR(20))  +' '+ BuildingName +' '+ Postcode) as CompleteAddress from addressRecord
where( CAST(BuildingNumber AS NVARCHAR(20)) + ' ' + BuildingName + ' ' + Postcode )like '%PL1 1AB%'
 
Share this answer
 
Comments
babli3 20-Aug-14 4:50am    
Hi
Thank You .
But when I am running this query it's giving me empty result.
But I do have values in the database for this postcode.

Many Thanks
babli3 20-Aug-14 4:53am    
Thank You it's working:)
OriginalGriff 20-Aug-14 4:55am    
You're welcome!
babli3 20-Aug-14 4:57am    
If one of the column is null then it is not displaying anything.
Please could you suggest how to resolve this.Thanks
OriginalGriff 20-Aug-14 5:02am    
Use ISNULL:
http://msdn.microsoft.com/en-us/library/ms184325.aspx

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