Меню

Ошибка при преобразовании типа данных varchar к real

RRS feed

  • Remove From My Forums

  • Question

  • Hi guys.

    I have a varchar column in my database, it contains numbers like ‘-0.0001416882,-6.446603E-05,2.401768E-05,1.114732E-05………’

    I’m trying to add a WHERE clause, so that it only show the numbers that fall between the range, for example form 0.0001 to 0.0002

    Unfortunately I got ‘Error converting data type varchar to real.’ error while running the query.

    Please advice.

All replies

  • This error cause may be two reasons
     1. -6.446603E-05 «E» is exists in the number
     2. In data there may be non numeric characters exists.

    check below select statment

    SELECT * FROM table
    WHERE ISNUMERIC(col) = 0

    update the column to remove characters to number
    update table
    set [col]=CASE WHEN CHARINDEX(‘E’, col) != 0 THEN CAST(CAST(col AS REAL) AS DECIMAL(15,5)) ELSE CAST(col AS DECIMAL(15,5)) END
    where isnumeric([col]) = 1

    REPLACE(CHAR(10), col, »)

    • Proposed as answer by

      Tuesday, June 29, 2010 8:33 AM

  • Dear sekhara,

    Thank you very much for your help.

    I tried to write the query the way you did, it works!!!!!

    select row
    from tbl_Point
    WHERE ((CASE WHEN CHARINDEX(‘E’, row) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(rowAS DECIMAL(15,15)) END <=0.00011) AND

      (CASE WHEN CHARINDEX(‘E’, row) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=0.000011))

    But when I used it in dynamic query with the below, I got the same error — Error converting data type varchar to real. Any idea?

    SET @strWhere =’SELECT row FROM tbl_Point WHERE ‘
    SET @strWhere = @strWhere +'((CASE WHEN CHARINDEX(»»E»»,  row ) != 0 THEN CAST(CAST( row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END <=’+@prmUpperLimit+’) AND

      (CASE WHEN CHARINDEX(»»E»»,  + row + ‘) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=’+@prmLowerLimit+’))’

  • Remove the four (»»E»») keep two (»E»)

    SET

    @strWhere =‘SELECT
    row FROM tbl_Point WHERE ‘

    SET

    @strWhere = @strWhere
    +‘((CASE WHEN CHARINDEX(»E», row ) != 0 THEN CAST(CAST(
    row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END <=’    +@prmUpperLimit+‘)
    AND

    (CASE WHEN CHARINDEX(»E», + row + ) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=’

    +@prmLowerLimit+‘))’

    • Proposed as answer by
      sekhara shiris chinta
      Thursday, July 8, 2010 8:18 AM

Источник

RRS feed

  • Remove From My Forums

  • Question

  • Hi guys.

    I have a varchar column in my database, it contains numbers like ‘-0.0001416882,-6.446603E-05,2.401768E-05,1.114732E-05………’

    I’m trying to add a WHERE clause, so that it only show the numbers that fall between the range, for example form 0.0001 to 0.0002

    Unfortunately I got ‘Error converting data type varchar to real.’ error while running the query.

    Please advice.

All replies

  • This error cause may be two reasons
     1. -6.446603E-05 «E» is exists in the number
     2. In data there may be non numeric characters exists.

    check below select statment

    SELECT * FROM table
    WHERE ISNUMERIC(col) = 0

    update the column to remove characters to number
    update table
    set [col]=CASE WHEN CHARINDEX(‘E’, col) != 0 THEN CAST(CAST(col AS REAL) AS DECIMAL(15,5)) ELSE CAST(col AS DECIMAL(15,5)) END
    where isnumeric([col]) = 1

    REPLACE(CHAR(10), col, »)

    • Proposed as answer by

      Tuesday, June 29, 2010 8:33 AM

  • Dear sekhara,

    Thank you very much for your help.

    I tried to write the query the way you did, it works!!!!!

    select row
    from tbl_Point
    WHERE ((CASE WHEN CHARINDEX(‘E’, row) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(rowAS DECIMAL(15,15)) END <=0.00011) AND

      (CASE WHEN CHARINDEX(‘E’, row) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=0.000011))

    But when I used it in dynamic query with the below, I got the same error — Error converting data type varchar to real. Any idea?

    SET @strWhere =’SELECT row FROM tbl_Point WHERE ‘
    SET @strWhere = @strWhere +'((CASE WHEN CHARINDEX(»»E»»,  row ) != 0 THEN CAST(CAST( row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END <=’+@prmUpperLimit+’) AND

      (CASE WHEN CHARINDEX(»»E»»,  + row + ‘) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=’+@prmLowerLimit+’))’

  • Remove the four (»»E»») keep two (»E»)

    SET

    @strWhere =‘SELECT
    row FROM tbl_Point WHERE ‘

    SET

    @strWhere = @strWhere
    +‘((CASE WHEN CHARINDEX(»E», row ) != 0 THEN CAST(CAST(
    row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END <=’    +@prmUpperLimit+‘)
    AND

    (CASE WHEN CHARINDEX(»E», + row + ) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=’

    +@prmLowerLimit+‘))’

    • Proposed as answer by
      sekhara shiris chinta
      Thursday, July 8, 2010 8:18 AM

Источник

DenKG

10 / 10 / 7

Регистрация: 23.12.2015

Сообщений: 950

1

31.12.2017, 14:50. Показов 2913. Ответов 2

Метки нет (Все метки)

Пытаюсь обновить запись в БД.
Делаю так:

C# 
1
2

double stavka = Convert.ToDouble(textBox3.Text);
SqlCommand updateTeacher2 = new SqlCommand("Update [Преподаватели_год] Set Ставка = '" + stavka + "' where ID_преподавателя = '" + teacherID + "'", MyConnection);

И в этом случае вылетает ошибка при преобразовании типа данных varchar к real.
Если же изменяю с 1,25 на 1, то всё работает.

Что тут не так?

В БД поле «Ставка» имеет тип данных Real.

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь



0

Programming

Эксперт

94731 / 64177 / 26122

Регистрация: 12.04.2006

Сообщений: 116,782

 31.12.2017, 14:50

2

OwenGlendower

Администратор

Эксперт .NET

15226 / 12265 / 4902

Регистрация: 17.03.2014

Сообщений: 24,867

Записей в блоге: 1

31.12.2017, 14:59

2

DenKG, зачем преобразовывать строку в double чтобы подставить в запрос в виде строки? Используй параметры:

C# 
1
2
3
4

double stavka = Convert.ToDouble(textBox3.Text);
SqlCommand updateTeacher2 = new SqlCommand("Update [Преподаватели_год] Set Ставка = @Ставка where ID_преподавателя = @ID_преподавателя", MyConnection);
updateTeacher2.Parameters.AddWithValue"(@Ставка", stavka);
updateTeacher2.Parameters.AddWithValue("@ID_преподавателя", teacherID);



1

10 / 10 / 7

Регистрация: 23.12.2015

Сообщений: 950

31.12.2017, 15:01

 [ТС]

3

OwenGlendower, благодарю



0
Источник

RRS feed

  • Remove From My Forums

  • Question

  • Hi guys.

    I have a varchar column in my database, it contains numbers like ‘-0.0001416882,-6.446603E-05,2.401768E-05,1.114732E-05………’

    I’m trying to add a WHERE clause, so that it only show the numbers that fall between the range, for example form 0.0001 to 0.0002

    Unfortunately I got ‘Error converting data type varchar to real.’ error while running the query.

    Please advice.

All replies

  • This error cause may be two reasons
     1. -6.446603E-05 «E» is exists in the number
     2. In data there may be non numeric characters exists.

    check below select statment

    SELECT * FROM table
    WHERE ISNUMERIC(col) = 0

    update the column to remove characters to number
    update table
    set [col]=CASE WHEN CHARINDEX(‘E’, col) != 0 THEN CAST(CAST(col AS REAL) AS DECIMAL(15,5)) ELSE CAST(col AS DECIMAL(15,5)) END
    where isnumeric([col]) = 1

    REPLACE(CHAR(10), col, »)

    • Proposed as answer by

      Tuesday, June 29, 2010 8:33 AM

  • Dear sekhara,

    Thank you very much for your help.

    I tried to write the query the way you did, it works!!!!!

    select row
    from tbl_Point
    WHERE ((CASE WHEN CHARINDEX(‘E’, row) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(rowAS DECIMAL(15,15)) END <=0.00011) AND

      (CASE WHEN CHARINDEX(‘E’, row) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=0.000011))

    But when I used it in dynamic query with the below, I got the same error — Error converting data type varchar to real. Any idea?

    SET @strWhere =’SELECT row FROM tbl_Point WHERE ‘
    SET @strWhere = @strWhere +'((CASE WHEN CHARINDEX(»»E»»,  row ) != 0 THEN CAST(CAST( row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END <=’+@prmUpperLimit+’) AND

      (CASE WHEN CHARINDEX(»»E»»,  + row + ‘) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=’+@prmLowerLimit+’))’

  • Remove the four (»»E»») keep two (»E»)

    SET

    @strWhere =‘SELECT
    row FROM tbl_Point WHERE ‘

    SET

    @strWhere = @strWhere
    +‘((CASE WHEN CHARINDEX(»E», row ) != 0 THEN CAST(CAST(
    row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END <=’    +@prmUpperLimit+‘)
    AND

    (CASE WHEN CHARINDEX(»E», + row + ) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=’

    +@prmLowerLimit+‘))’

    • Proposed as answer by
      sekhara shiris chinta
      Thursday, July 8, 2010 8:18 AM

Источник
  • Remove From My Forums

  • Вопрос

  • I’m working with a pretty massive data set here.  I did a Bulk Insert and got everything loaded into a staging table.  Now, everything comes in as VARCHAR.  You don’t really have a lot of control over it during the Bulk Insert.  I just
    created a new production table:

    CREATE
    TABLE [dbo].[CA_Final]

    (

    [Date] DATETIME,

         
    [cusip] VARCHAR(100),

         
    [sedol] VARCHAR(100),

    etc.

    Insert Into [dbo].[CA_Final]

    SELECT

    [Date],

         
    [cusip],

         
    [sedol],

    etc.

    From StagingTable

    Now I’m getting this error.

    Msg 8114, Level 16, State 5, Line 358
    Error converting data type varchar to real.

    I’ve done this before, and it worked fine.  That was a while back, and I forgot what I did to get it working.  Can someone point me in the right direction here?

    Thanks!

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Ответы

  • Have you inspected those row/column’s?

    They must have something in them preventing them from being converted. White space? Tabs?

    • Помечено в качестве ответа

      12 сентября 2014 г. 23:01

  • Hummmm, looks like float did the trick.

    Thanks Patrick.  Thanks everyone.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Помечено в качестве ответа
      Lydia ZhangMicrosoft contingent staff
      10 октября 2014 г. 9:53

Источник
  • Remove From My Forums

  • Question

  • I’m working with a pretty massive data set here.  I did a Bulk Insert and got everything loaded into a staging table.  Now, everything comes in as VARCHAR.  You don’t really have a lot of control over it during the Bulk Insert.  I just
    created a new production table:

    CREATE
    TABLE [dbo].[CA_Final]

    (

    [Date] DATETIME,

         
    [cusip] VARCHAR(100),

         
    [sedol] VARCHAR(100),

    etc.

    Insert Into [dbo].[CA_Final]

    SELECT

    [Date],

         
    [cusip],

         
    [sedol],

    etc.

    From StagingTable

    Now I’m getting this error.

    Msg 8114, Level 16, State 5, Line 358
    Error converting data type varchar to real.

    I’ve done this before, and it worked fine.  That was a while back, and I forgot what I did to get it working.  Can someone point me in the right direction here?

    Thanks!

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answers

  • Have you inspected those row/column’s?

    They must have something in them preventing them from being converted. White space? Tabs?

    • Marked as answer by

      Friday, September 12, 2014 11:01 PM

  • Hummmm, looks like float did the trick.

    Thanks Patrick.  Thanks everyone.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by
      Lydia ZhangMicrosoft contingent staff
      Friday, October 10, 2014 9:53 AM

Источник
  • Remove From My Forums

  • Question

  • I’m working with a pretty massive data set here.  I did a Bulk Insert and got everything loaded into a staging table.  Now, everything comes in as VARCHAR.  You don’t really have a lot of control over it during the Bulk Insert.  I just
    created a new production table:

    CREATE
    TABLE [dbo].[CA_Final]

    (

    [Date] DATETIME,

         
    [cusip] VARCHAR(100),

         
    [sedol] VARCHAR(100),

    etc.

    Insert Into [dbo].[CA_Final]

    SELECT

    [Date],

         
    [cusip],

         
    [sedol],

    etc.

    From StagingTable

    Now I’m getting this error.

    Msg 8114, Level 16, State 5, Line 358
    Error converting data type varchar to real.

    I’ve done this before, and it worked fine.  That was a while back, and I forgot what I did to get it working.  Can someone point me in the right direction here?

    Thanks!

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answers

  • Have you inspected those row/column’s?

    They must have something in them preventing them from being converted. White space? Tabs?

    • Marked as answer by

      Friday, September 12, 2014 11:01 PM

  • Hummmm, looks like float did the trick.

    Thanks Patrick.  Thanks everyone.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by
      Lydia ZhangMicrosoft contingent staff
      Friday, October 10, 2014 9:53 AM

Источник

SQL Server 2012 and Later

Just use Try_Convert instead:

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Read more about Try_Convert.

SQL Server 2008 and Earlier

The traditional way of handling this is by guarding every expression with a case statement so that no matter when it is evaluated, it will not create an error, even if it logically seems that the CASE statement should not be needed. Something like this:

SELECT
   Account_Code =
      Convert(
         bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must
         CASE
         WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
         ELSE X.Account_Code
         END
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      CASE
      WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
      ELSE X.Account_Code
      END
   ) BETWEEN 503100 AND 503205

However, I like using strategies such as this with SQL Server 2005 and up:

SELECT
   Account_Code = Convert(bigint, X.Account_Code),
   A.Descr
FROM
   dbo.Account A
   OUTER APPLY (
      SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%[^0-9]%'
   ) X
WHERE
   Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205

What this does is strategically switch the Account_Code values to NULL inside of the X table when they are not numeric. I initially used CROSS APPLY but as Mikael Eriksson so aptly pointed out, this resulted in the same error because the query parser ran into the exact same problem of optimizing away my attempt to force the expression order (predicate pushdown defeated it). By switching to OUTER APPLY it changed the actual meaning of the operation so that X.Account_Code could contain NULL values within the outer query, thus requiring proper evaluation order.

You may be interested to read Erland Sommarskog’s Microsoft Connect request about this evaluation order issue. He in fact calls it a bug.

There are additional issues here but I can’t address them now.

P.S. I had a brainstorm today. An alternate to the «traditional way» that I suggested is a SELECT expression with an outer reference, which also works in SQL Server 2000. (I’ve noticed that since learning CROSS/OUTER APPLY I’ve improved my query capability with older SQL Server versions, too—as I am getting more versatile with the «outer reference» capabilities of SELECT, ON, and WHERE clauses!) 

SELECT
   Account_Code =
      Convert(
         bigint,
         (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
   ) BETWEEN 503100 AND 503205

It’s a lot shorter than the CASE statement.

Источник

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest

0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии

А вот еще интересные материалы:

  • Яшка сломя голову остановился исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного где ошибка
  • Ошибка при преобразовании типа данных varchar к int
  • Ошибка при преобразовании типа данных varchar к float sql

    • ➤