Sql query to combine two columns throwing error

Question

3.00/5 (1 vote)

See more:

SQL-Server-2008R2

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 19-Aug-14 22:38pm

babli3

Add a Solution

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)

OriginalGriff · Accepted Answer · 2014-08-19T22:44:00

Solution 1

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%'

Posted 19-Aug-14 22:44pm

OriginalGriff

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.