Conversion

9 Sep 2005 11:54 AM

Chandra

Hi,

Can I convert an nvarchar field to numeric type?

Thanks
Chandra

9 Sep 2005 11:58 AM

Jose G. de Jesus Jr MCP, MCDBA

yes

--
thanks,

------------------------------------
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787

Show quote

"Chandra" wrote:

> Hi,
>
> Can I convert an nvarchar field to numeric type?
>
> Thanks
> Chandra

9 Sep 2005 12:00 PM

RioDD

try
Cast (myNvarCharTableColumn as numeric)

Show quote

"Chandra" wrote:

> Hi,
>
> Can I convert an nvarchar field to numeric type?
>
> Thanks
> Chandra

9 Sep 2005 1:04 PM

Hari Prasad

Hi,

declare @t nvarchar(10)
set @t='10'
select convert(numeric(5,2),@t)

Ensure that you have no character data in te column

Thanks
Hari
SQL Server MVP

Show quote

"RioDD" <Ri***@discussions.microsoft.com> wrote in message
news:641160B8-BA5D-4FF4-92E3-76C39B2FA79B@microsoft.com...
> try
> Cast (myNvarCharTableColumn as numeric)
>
> "Chandra" wrote:
>
>> Hi,
>>
>> Can I convert an nvarchar field to numeric type?
>>
>> Thanks
>> Chandra

9 Sep 2005 1:04 PM

Chandra

I tried Hari's solution but it is giving the following error.

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

what could be the reason?

Thanks
Chandra

Show quote

"Hari Prasad" wrote:

> Hi,
>
> declare @t nvarchar(10)
> set @t='10'
> select convert(numeric(5,2),@t)
>
> Ensure that you have no character data in te column
>
> Thanks
> Hari
> SQL Server MVP
>
> "RioDD" <Ri***@discussions.microsoft.com> wrote in message
> news:641160B8-BA5D-4FF4-92E3-76C39B2FA79B@microsoft.com...
> > try
> > Cast (myNvarCharTableColumn as numeric)
> >
> > "Chandra" wrote:
> >
> >> Hi,
> >>
> >> Can I convert an nvarchar field to numeric type?
> >>
> >> Thanks
> >> Chandra
>
>
>

9 Sep 2005 1:24 PM

ML

Obviously your nvarchar column contains values that cannot be converted to
the numeric data type. Read my other post in this thread.

It's imperative that you test values before attempting to cast/convert them,
since in some circumstances conversions even if successful can lead to
unexpected results.

And when it comes to numbers certain aspects of life can become devastated
by such faults.

ML

9 Sep 2005 2:21 PM

Aaron Bertrand [SQL Server MVP]

>I tried Hari's solution but it is giving the following error.
>
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type nvarchar to numeric.
>
> what could be the reason?

The reason is that you have non-numeric data in your NVARCHAR column. How
is this supposed to work?

SELECT CONVERT(NUMERIC(5,2), 'fooblat$%xpq@g')

?

9 Sep 2005 4:18 PM

ML

Don't you know that 'fooblat$%xpq@g' = 42?

:)

ML

9 Sep 2005 5:14 PM

Aaron Bertrand [SQL Server MVP]

Of course, I do. But I haven't applied that specific hotfix to my
production SQL Servers yet. :-)

Show quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:D5B6DD32-14D3-4413-998F-8F2789EE7747@microsoft.com...
> Don't you know that 'fooblat$%xpq@g' = 42?
>
> :)
>
>
> ML

9 Sep 2005 5:48 PM

ML

Storing salaries as varchar might not be such a bad idea after all:

replace(salary, '.', 'e')

;)

ML

9 Sep 2005 12:25 PM

ML

Also look at this recent thread for some details on numeric data types: http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=caa4c151-1c7b-4b05-8f71-b7768b43fcf3

....in case you missed it.

ML