Click here to Skip to main content
14,696,341 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%'


error:

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

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:
select (CAST(BuildingNumber AS NVARCHAR(20))  +' '+ BuildingName +' '+ Postcode) as CompleteAddress from addressRecord
where( CAST(BuildingNumber AS NVARCHAR(20)) + ' ' + BuildingName + ' ' + Postcode )like '%PL1 1AB%'
   
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
babli3 20-Aug-14 5:19am
   
I Changed the query

select (CAST(ISNULL(BuildingNumber,0) AS NVARCHAR(20)) +', '+ ISNULL(BuildingName,0) +', '+ ISNULL(Postcode,0)) as CompleteAddress from AddressRecord
where( CAST(ISNULL(BuildingNumber,0) AS NVARCHAR(20)) +' ,'+ ISNULL(BuildingName,0) +' ,'+ ISNULL(Postcode,0) )like '%PL1 1DQ%'

But now the output I am getting is with 0 aswell.

output:
27, 0, PL1 1DQ

Please can u suggest me as I dont want this 0 to be displayed.

Many Thanks
Kumarbs 20-Aug-14 8:09am
   
Use this.

select (CAST(ISNULL(BuildingNumber,'') AS NVARCHAR(20)) +', '+ ISNULL(BuildingName,'') +', '+ ISNULL(Postcode,'')) as CompleteAddress from AddressRecord
where( CAST(ISNULL(BuildingNumber,'') AS NVARCHAR(20)) +' ,'+ ISNULL(BuildingName,'') +' ,'+ ISNULL(Postcode,'') )like '%PL1 1DQ%'
babli3 20-Aug-14 12:28pm
   
Thank You
babli3 20-Aug-14 12:37pm
   
Hi

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


Thanks
Kumarbs 21-Aug-14 0:29am
   
I think building number is numeric hence the issue. You can use this.
select (CAST(ISNULL(BuildingNumber,0) AS NVARCHAR(20)) +', '+ ISNULL(BuildingName,'') +', '+ ISNULL(Postcode,'')) as CompleteAddress from AddressRecord
where( CAST(ISNULL(BuildingNumber,0) AS NVARCHAR(20)) +' ,'+ ISNULL(BuildingName,'') +' ,'+ ISNULL(Postcode,'') )like '%PL1 1DQ%'
babli3 21-Aug-14 4:46am
   
Hi Kumar

This is the query which i used before and was giving me output with 0.
output:
27, 0, PL1 1DQ

I dont want this 0 to be displayed.

Many Thanks
Kumarbs 21-Aug-14 5:45am
   
In this query ISNULL is maintained for 0 for building number only. So this will get output correctly.

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