[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Concatenation headache SQL Server 2005 Express

jpBless

7/21/2007 11:25:00 AM

ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' + tbl_Employees.FirstName
As WDS FROM tbl_Employees"

I am trying to concatenate two fields in SQL Server Express 2005. Employees
number is int (Number) and Employees Firstname is varchar(String)

I get the error

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value 'Jason' to data type
int.

Please help... Thanks very much in advance
6 Answers

Dan Guzman

7/21/2007 11:48:00 AM

0

> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.

It seems your EmployeeNumber column is an int. Because int has a higher
data type precedence than nvarchar, FirstName is implicitly converted to int
and this fails because you have FirstName values that are not integers.
You can explicitly cast EmployeeNumber to nvarchar in order to perform
concatenation instead of addition and avoid the conversion error:

SELECT
CAST(tbl_Employees.EmployeeNumber AS nvarchar(10)) +
N' ' +
tbl_Employees.FirstName As WDS
FROM dbo.tbl_Employees

However, I suggest you do this concatenation in your application code rather
than SQL Server. Formatting data for display purposes is a task better done
in the presentation layer.

See the data type precedence topic in the SQL Server Books Online for more
information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
news:%230S1Cn4yHHA.5408@TK2MSFTNGP02.phx.gbl...
> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>
> I am trying to concatenate two fields in SQL Server Express 2005.
> Employees number is int (Number) and Employees Firstname is
> varchar(String)
>
> I get the error
>
> Msg 245, Level 16, State 1, Line 1
>
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
>
> Please help... Thanks very much in advance
>
>
>
>

jpBless

7/21/2007 12:05:00 PM

0

Great!!! Worked!!! Thanks for your help and insight. I appreciate it
"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:6EC71182-59F9-4780-B6DE-E64BA2AA4D64@microsoft.com...
>> Conversion failed when converting the nvarchar value 'Jason' to data type
>> int.
>
> It seems your EmployeeNumber column is an int. Because int has a higher
> data type precedence than nvarchar, FirstName is implicitly converted to
> int and this fails because you have FirstName values that are not
> integers. You can explicitly cast EmployeeNumber to nvarchar in order to
> perform concatenation instead of addition and avoid the conversion error:
>
> SELECT
> CAST(tbl_Employees.EmployeeNumber AS nvarchar(10)) +
> N' ' +
> tbl_Employees.FirstName As WDS
> FROM dbo.tbl_Employees
>
> However, I suggest you do this concatenation in your application code
> rather than SQL Server. Formatting data for display purposes is a task
> better done in the presentation layer.
>
> See the data type precedence topic in the SQL Server Books Online for more
> information.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
> news:%230S1Cn4yHHA.5408@TK2MSFTNGP02.phx.gbl...
>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>>
>> I am trying to concatenate two fields in SQL Server Express 2005.
>> Employees number is int (Number) and Employees Firstname is
>> varchar(String)
>>
>> I get the error
>>
>> Msg 245, Level 16, State 1, Line 1
>>
>> Conversion failed when converting the nvarchar value 'Jason' to data type
>> int.
>>
>> Please help... Thanks very much in advance
>>
>>
>>
>>
>


D@t@Mill

7/21/2007 3:05:00 PM

0

If you want to return a numeric value concatenated with alpha data implicit
conversions fail. So you need to convert() or cast(), both are very similar
although I prefer convert as you can explicitly define the full data type
including the length. I've included an example below.

SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
tbl_Employees.FirstName As WDS
FROM tbl_Employees

OR

SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
tbl_Employees.FirstName As WDS
FROM tbl_Employees

NOTE: you need to define the length of the nvarchar, which is typically the
length of the number.

"JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
news:%230S1Cn4yHHA.5408@TK2MSFTNGP02.phx.gbl...
> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>
> I am trying to concatenate two fields in SQL Server Express 2005.
> Employees number is int (Number) and Employees Firstname is
> varchar(String)
>
> I get the error
>
> Msg 245, Level 16, State 1, Line 1
>
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
>
> Please help... Thanks very much in advance
>
>
>
>

jpBless

7/21/2007 4:25:00 PM

0

Thanks very much... appreciate your help

> NOTE: you need to define the length of the nvarchar, which is typically
> the length of the number.

What if the length of nvarchar is unknown?

"D@t@Mill" <andrewrobertmiller@gmail.com> wrote in message
news:6398A987-FEE8-4923-9127-CB5883D4C28A@microsoft.com...
> If you want to return a numeric value concatenated with alpha data
> implicit conversions fail. So you need to convert() or cast(), both are
> very similar although I prefer convert as you can explicitly define the
> full data type including the length. I've included an example below.
>
> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
> tbl_Employees.FirstName As WDS
> FROM tbl_Employees
>
> OR
>
> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
> tbl_Employees.FirstName As WDS
> FROM tbl_Employees
>
>
> "JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
> news:%230S1Cn4yHHA.5408@TK2MSFTNGP02.phx.gbl...
>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>>
>> I am trying to concatenate two fields in SQL Server Express 2005.
>> Employees number is int (Number) and Employees Firstname is
>> varchar(String)
>>
>> I get the error
>>
>> Msg 245, Level 16, State 1, Line 1
>>
>> Conversion failed when converting the nvarchar value 'Jason' to data type
>> int.
>>
>> Please help... Thanks very much in advance
>>
>>
>>
>>
>


William Vaughn

7/21/2007 5:02:00 PM

0

Or, better yet, do the conversion on the client. It will help your query run
faster (as the SQL engine does not have to do the conversions and
concatenation).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
www.hitchhikerguides.net
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
news:eIyx9O7yHHA.3916@TK2MSFTNGP02.phx.gbl...
> Thanks very much... appreciate your help
>
>> NOTE: you need to define the length of the nvarchar, which is typically
>> the length of the number.
>
> What if the length of nvarchar is unknown?
>
> "D@t@Mill" <andrewrobertmiller@gmail.com> wrote in message
> news:6398A987-FEE8-4923-9127-CB5883D4C28A@microsoft.com...
>> If you want to return a numeric value concatenated with alpha data
>> implicit conversions fail. So you need to convert() or cast(), both are
>> very similar although I prefer convert as you can explicitly define the
>> full data type including the length. I've included an example below.
>>
>> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
>> tbl_Employees.FirstName As WDS
>> FROM tbl_Employees
>>
>> OR
>>
>> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
>> tbl_Employees.FirstName As WDS
>> FROM tbl_Employees
>>
>>
>> "JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
>> news:%230S1Cn4yHHA.5408@TK2MSFTNGP02.phx.gbl...
>>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>>>
>>> I am trying to concatenate two fields in SQL Server Express 2005.
>>> Employees number is int (Number) and Employees Firstname is
>>> varchar(String)
>>>
>>> I get the error
>>>
>>> Msg 245, Level 16, State 1, Line 1
>>>
>>> Conversion failed when converting the nvarchar value 'Jason' to data
>>> type int.
>>>
>>> Please help... Thanks very much in advance
>>>
>>>
>>>
>>>
>>
>
>

jpBless

7/21/2007 5:19:00 PM

0

Thanks...


"William Vaughn" <billvaNoSPAM@betav.com> wrote in message
news:000D6EA4-77BC-4D8E-8A41-1D52639C99B4@microsoft.com...
> Or, better yet, do the conversion on the client. It will help your query
> run faster (as the SQL engine does not have to do the conversions and
> concatenation).
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> www.hitchhikerguides.net
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> -----------------------------------------------------------------------------------------------------------------------
>
> "JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
> news:eIyx9O7yHHA.3916@TK2MSFTNGP02.phx.gbl...
>> Thanks very much... appreciate your help
>>
>>> NOTE: you need to define the length of the nvarchar, which is typically
>>> the length of the number.
>>
>> What if the length of nvarchar is unknown?
>>
>> "D@t@Mill" <andrewrobertmiller@gmail.com> wrote in message
>> news:6398A987-FEE8-4923-9127-CB5883D4C28A@microsoft.com...
>>> If you want to return a numeric value concatenated with alpha data
>>> implicit conversions fail. So you need to convert() or cast(), both are
>>> very similar although I prefer convert as you can explicitly define the
>>> full data type including the length. I've included an example below.
>>>
>>> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
>>> tbl_Employees.FirstName As WDS
>>> FROM tbl_Employees
>>>
>>> OR
>>>
>>> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
>>> tbl_Employees.FirstName As WDS
>>> FROM tbl_Employees
>>>
>>>
>>> "JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
>>> news:%230S1Cn4yHHA.5408@TK2MSFTNGP02.phx.gbl...
>>>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>>>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>>>>
>>>> I am trying to concatenate two fields in SQL Server Express 2005.
>>>> Employees number is int (Number) and Employees Firstname is
>>>> varchar(String)
>>>>
>>>> I get the error
>>>>
>>>> Msg 245, Level 16, State 1, Line 1
>>>>
>>>> Conversion failed when converting the nvarchar value 'Jason' to data
>>>> type int.
>>>>
>>>> Please help... Thanks very much in advance
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>