Tags: cast, character, column, convert, database, deals, int, microsoft, mysql, number, numeric, oracle, server, specifically, sql, varchar

Convert blank to numeric vs. int

On Database » Microsoft SQL Server

2,282 words with 1 Comments; publish: Mon, 02 Jun 2008 02:20:00 GMT; (250125.00, « »)

I am trying to understand how CONVERT / CAST works from

character data to a number. The issue specifically deals

with a VARCHAR column that has nothing in it (blank, not

null). When a CONVERT function is used on this column to

convert the values in the column to DECIMAL(20,0) or

NUMERIC, I get an error. However, if I convert the column

to INT, the blank values show up as 0.

Can someone explain why this is, or if there is a problem

I am unaware of. I would think that the DECIMAL or

NUMERIC data types would handle the blank value the same

way.

Thanks

All Comments

Leave a comment...

  • 1 Comments
    • From the SQL Server 2000 Books Online:

      <Excerpt href="/app/links/?link=tsqlref.chm::/ts_ca-co_2f3o.htm">

      SQL Server returns an error message when non-numeric char, nchar,

      varchar, or nvarchar data is converted to int, float, numeric, or

      decimal. SQL Server also returns an error when an empty string (" ") is

      converted to numeric or decimal.

      </Excerpt>

      It seems that decimal and numeric are special cases when converting

      empty strings.

      --

      Hope this helps.

      Dan Guzman

      SQL Server MVP

      --

      SQL FAQ links (courtesy Neil Pike):

      http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800

      http://www.sqlserverfaq.com

      http://www.mssqlserver.com/faq

      --

      "Nate" <nate.tewinkel.sqlserver.todaysummary.com.iti.fiserv.com> wrote in message

      news:772b01c37e21$ae9d97d0$a601280a.sqlserver.todaysummary.com.phx.gbl...

      > I am trying to understand how CONVERT / CAST works from

      > character data to a number. The issue specifically deals

      > with a VARCHAR column that has nothing in it (blank, not

      > null). When a CONVERT function is used on this column to

      > convert the values in the column to DECIMAL(20,0) or

      > NUMERIC, I get an error. However, if I convert the column

      > to INT, the blank values show up as 0.

      > Can someone explain why this is, or if there is a problem

      > I am unaware of. I would think that the DECIMAL or

      > NUMERIC data types would handle the blank value the same

      > way.

      > Thanks

      >

      #1; Mon, 02 Jun 2008 02:21:00 GMT