Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

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.
Avatar of hielo
hielo
Flag of Wallis and Futuna image

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

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?
Avatar of Aleks

ASKER

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.
Which database are you using? You might need a different operator for concatenating your strings. (It might not be a plus sign.)
>> 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'
Avatar of Aleks

ASKER

sql 2012
Avatar of Aleks

ASKER

I did the issue is in the value being displayed
ASKER CERTIFIED SOLUTION
Avatar of Jan Louwerens
Jan Louwerens
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>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.
Avatar of Aleks

ASKER

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

Avatar of Norie
Norie

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.