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.

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


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


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

Please can anyone help me.

Many Thanks

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.

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
babli3 20-Aug-14 4:50am    
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    

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