Syntax error in SQL

I have the following SQL

SELECT a.UserId, 'contact' AS UserType, + 'Contact |' +  ' ' + a.FirstNm + ' ' + a.LastNm + ' ' + '(' + a.contact_id_interntal + ')'   AS result
FROM dbo.Users AS a
WHERE (a.contact_id_interntal = 50002) AND a.UserType = 'contact'

Open in new window


contact_id_interntal  is an INT, so I am getting an error. I imagine I need to convert it into varchar. How can I do this?
Below is the error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ')' to data type int.

Open in new window


Help is appreciated.
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
>> SELECT a.UserId, 'contact' AS UserType, + 'Contact |' ...
Try getting rid of the leading "+" to the left of 'Contact |'

SELECT a.UserId, 'contact' AS UserType,  'Contact |  ' + a.FirstNm + ' ' + a.LastNm + ' (' + a.contact_id_interntal + ')'   AS result
FROM dbo.Users AS a
WHERE (a.contact_id_interntal = 50002) AND a.UserType = 'contact'

Open in new window

0
Jan LouwerensSoftware EngineerCommented:
I think it might be the first plus sign (before 'Contact |') in your select query. That might be confusing the query parser, thinking it might be a number since it's starting with a plus sign.

SELECT a.UserId, 'contact' AS UserType, 'Contact |' +  ' ' + a.FirstNm + ' ' + a.LastNm + ' ' + '(' + a.contact_id_interntal + ')'   AS result
FROM dbo.Users AS a
WHERE (a.contact_id_interntal = 50002) AND a.UserType = 'contact'

Open in new window


Also, are you sure the the + operator is the string concatenator in the database you're using?
0
AleksAuthor Commented:
Neither queries worked. If I change the 'a.contact_id_interntal ' for a varchar field it works fine.
I think I need to convert that integer field to varchar to avoid the issue.

So far the error was the same.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Jan LouwerensSoftware EngineerCommented:
Which database are you using? You might need a different operator for concatenating your strings. (It might not be a plus sign.)
0
hieloCommented:
>> If I change the 'a.contact_id_interntal ' for a varchar field it works fine
Did you try quoting the number?
a.contact_id_interntal = '50002'
0
AleksAuthor Commented:
sql 2012
0
AleksAuthor Commented:
I did the issue is in the value being displayed
0
Jan LouwerensSoftware EngineerCommented:
SELECT a.UserId, 'contact' AS UserType, 'Contact |' +  ' ' + a.FirstNm + ' ' + a.LastNm + ' ' + '(' + CAST(a.contact_id_interntal AS varchar) + ')'   AS result
FROM dbo.Users AS a
WHERE (a.contact_id_interntal = 50002) AND a.UserType = 'contact'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
>Conversion failed when converting the varchar value ')' to data type int.
I assume this is at the place

a.contact_id_interntal + ')'

Line 1 is reported, isn't it?

So, if a.contact_id_interntal is an integer column, you need CAST(a.contact_id_interntal as varchar(x))+')' at that place.

You could ask why the error message didn't already report '(' + a.contact_id_interntal. Well, it seems string+int would work, but not string+int+string.

Anyway, when you combine fields to an overall string it's best to cast or use other type conversion functions on any column not string/varchar itself, already. Don't depend on implicit type conversions.

Bye, Olaf.
0
AleksAuthor Commented:
Did some research and tested and as I though I had to convert it to varchar.
This worked

SELECT a.UserId, 'contact' AS UserType, 'Contact |' +  ' ' + a.FirstNm + ' ' + a.LastNm + ' ' + '(' + cast(a.contact_id_interntal as varchar(100)) + ')'   AS result
FROM dbo.Users AS a
WHERE (a.contact_id_interntal = 50002) AND a.UserType = 'contact'

Open in new window

0
NorieData ProcessorCommented:
Try this.

SELECT a.UserId, 'contact' AS UserType, + 'Contact |' +  ' ' + a.FirstNm + ' ' + a.LastNm + ' ' + '(' + CAST(a.contact_id_internal AS varchar) + ')'   AS result
FROM dbo.Users AS a
WHERE (a.contact_id_internal = 50002) AND a.UserType = 'contact'

Note I changed contact_id_interntal to contact_id_internal as it looked like a typo, if it's not change it back.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.