Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 ошибка при конвертации  [new]
Shvonder
Member

Откуда:
Сообщений: 14
Доброго времени суток!
При конвертации varchar поля (вида 1234.56) в decimal выдается ошибка типа 'The char value has incorrect syntax' вообщем где-то данные содержат неподходящий символ или знак. Как можно найти поле в котором невозможна конвертация?

Заранее благодарю.
25 апр 07, 17:54    [4070274]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
Sergej
Member

Откуда: Mainz, Germany
Сообщений: 316
ISNUMERIC
25 апр 07, 18:02    [4070331]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
LIKE
25 апр 07, 18:10    [4070385]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
Shvonder
Member

Откуда:
Сообщений: 14
Странно, но:

update trans_all
set postvalue=cast(amount as decimal(32,2))
where isnumeric(amount)=1

Выдает: Error converting data type varchar to numeric. Формат поля postvalue - decimal(32,2).
25 апр 07, 18:36    [4070517]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Что вернёт
SELECT amount
FROM trans_all
WHERE amount LIKE '%[^-+. 0-9]%'
?
25 апр 07, 19:21    [4070743]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
Shvonder
Member

Откуда:
Сообщений: 14
Вернуло пару строчек со значением -2.057E+09

Т.е. получается, что ISNUMERIC понимает, что это цифра, а CAST конвертировать не хочет...

Большое спасибо.
25 апр 07, 19:38    [4070801]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Shvonder
Вернуло пару строчек со значением -2.057E+09

Т.е. получается, что ISNUMERIC понимает, что это цифра, а CAST конвертировать не хочет...

Большое спасибо.
ISNUMERIC возвращает 1, если строку можно сконвертировать в один из типов: INT, DECIMAL. FLOAT, MONEY и т.п. Приведённая Вами строка запросто конвертируется во FLOAT (INUMERIC=1), но в DECIMAL её сконвертировать нельзя.
Так что ценность функции ISNUMERIC близка к нулю.
25 апр 07, 20:18    [4070963]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
iap
Так что ценность функции ISNUMERIC близка к нулю
Я вчера погорячился. Всё-таки, функция ISNUMERIC полезна, когда надо проверить не только наличие некоторых символов в строке перед конвертированием (с помощью LIKE), а и их количество, порядок следования в строке. Видимо, есть смысл использовать сочетание ISNUMERIC и LIKE перпед операцией конвертирования в определённый числовой тип. Например, так:
USE tempdb;
IF OBJECT_ID('T','U') IS NOT NULL DROP TABLE T;
CREATE TABLE T(ID INT NOT NULL IDENTITY, S CHAR(100) NOT NULL);
INSERT T(S)
      SELECT ' -542134 '
UNION SELECT ' -532 -'
UNION SELECT ' +000 -'
UNION SELECT '+6354'
UNION SELECT '++8956754'
UNION SELECT ' +2077.0954'
UNION SELECT ' -1114.5984 '
UNION SELECT ' +5675.12E-08 '
UNION SELECT '  .12E+2 '
UNION SELECT ' $543.12'
UNION SELECT '   €2870.12 '
UNION SELECT ' ye65435b88+09 '
UNION SELECT ' e65435e+09 '
UNION SELECT ' 65.43.5e+09 '

SELECT * FROM T;
SELECT ID, CAST(S AS INT) [INTEGER] FROM T WHERE ISNUMERIC(S)=1 AND S NOT LIKE '%[^-+ 0-9]%';
SELECT ID, CAST(S AS FLOAT) [FLOAT] FROM T WHERE ISNUMERIC(S)=1 AND S NOT LIKE '%[^-+.eE 0-9]%';
SELECT ID, CAST(S AS DECIMAL(10,4)) [DECIMAL] FROM T WHERE ISNUMERIC(S)=1 AND S NOT LIKE '%[^-+. 0-9]%';
SELECT ID, CAST(S AS MONEY) [MONEY] FROM T WHERE ISNUMERIC(S)=1 AND S NOT LIKE '%[eE]%';

IF OBJECT_ID('T','U') IS NOT NULL DROP TABLE T;
Хотя, может, я неправ...
26 апр 07, 18:25    [4075828]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
iap
Хотя, может, я неправ...
Так и есть. Неправ. FLOAT и DECIMAL без цифр не бывают. Исправляюсь (может, кому-нибудь пригодится):
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('T','U') IS NOT NULL DROP TABLE T;
CREATE TABLE T(ID INT NOT NULL IDENTITY, S CHAR(100) NOT NULL);
INSERT T(S)
      SELECT ' -542134 '
UNION SELECT '$'
UNION SELECT '+'
UNION SELECT ','
UNION SELECT '-'
UNION SELECT '.'
UNION SELECT '€'
UNION SELECT CHAR(160)
UNION SELECT '¤'
UNION SELECT '\'
UNION SELECT ' -532 -'
UNION SELECT ' +000 -'
UNION SELECT '+6354'
UNION SELECT ' +2077.0954'
UNION SELECT ' -1114.5984 '
UNION SELECT ' +5675.12E-08 '
UNION SELECT ' E-08 '
UNION SELECT ' E '
UNION SELECT '  .12E+2 '
UNION SELECT ' $543.12'
UNION SELECT '   €2870.12 '
UNION SELECT '++8956754'
UNION SELECT ' ye65435b88+09 '
UNION SELECT ' e65435e+09 '
UNION SELECT ' 65.43.5e+09 '

SELECT * FROM T;
SELECT * FROM T WHERE ISNUMERIC(S)=1;
SELECT ID, CAST(S AS INT) [INTEGER] FROM T WHERE ISNUMERIC(S)=1 AND S NOT LIKE '%[^-+ 0-9]%';
SELECT ID, CAST(S AS FLOAT) [FLOAT] FROM T WHERE ISNUMERIC(S)=1 AND S LIKE '%[0-9]%' AND S NOT LIKE '%[^-+.eE 0-9]%';
SELECT ID, CAST(S AS DECIMAL(10,4)) [DECIMAL] FROM T WHERE ISNUMERIC(S)=1 AND S LIKE '%[0-9]%' AND S NOT LIKE '%[^-+. 0-9]%';
SELECT ID, CAST(S AS MONEY) [MONEY] FROM T WHERE ISNUMERIC(S)=1 AND S NOT LIKE '%[eE]%';

IF OBJECT_ID('T','U') IS NOT NULL DROP TABLE T;
26 апр 07, 19:24    [4076052]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
мерси
27 июн 07, 13:36    [4320701]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: ошибка при конвертации  [new]
ne_ponjal
Guest
iap
Shvonder
Вернуло пару строчек со значением -2.057E+09

Т.е. получается, что ISNUMERIC понимает, что это цифра, а CAST конвертировать не хочет...

Большое спасибо.
ISNUMERIC возвращает 1, если строку можно сконвертировать в один из типов: INT, DECIMAL. FLOAT, MONEY и т.п. Приведённая Вами строка запросто конвертируется во FLOAT (INUMERIC=1), но в DECIMAL её сконвертировать нельзя.
Так что ценность функции ISNUMERIC близка к нулю.

а почему не конвертируется в decimal(32,2)?
у меня 2008-ой конвертирует,
ну и если даже без сервера не понимаю, почему это его в decimal(32,2) нельзя сконвертировать?
26 мар 12, 17:17    [12316037]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
ne_ponjal
у меня 2008-ой конвертирует
select cast ('-2.057E+09' as decimal (32,2))
---------------------------------------
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
26 мар 12, 17:29    [12316143]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
ne_ponjal
Guest
теперь еще больше не понимаю.
ну да, у меня он сконвертировал, если это ему без кавычек отдать.
а почему когда строка, то не конвертирует?
раз когда оно число, то пишется абсолютно так же
26 мар 12, 17:42    [12316242]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
ne_ponjal
теперь еще больше не понимаю.
ну да, у меня он сконвертировал, если это ему без кавычек отдать.
а почему когда строка, то не конвертирует?
раз когда оно число, то пишется абсолютно так же
Потому что FLOAT конвертируется в DEC() не по тем правилам,
по которым VARCHAR() конвертируется в DEC()
26 мар 12, 17:44    [12316258]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
ne_ponjal
теперь еще больше не понимаю.
ну да, у меня он сконвертировал, если это ему без кавычек отдать.
а почему когда строка, то не конвертирует?
раз когда оно число, то пишется абсолютно так же
С кавычками - это варчар. Без кавычек - это float. Как уже сказали, правила конвертации разные.
26 мар 12, 17:46    [12316276]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
iap
ne_ponjal
теперь еще больше не понимаю.
ну да, у меня он сконвертировал, если это ему без кавычек отдать.
а почему когда строка, то не конвертирует?
раз когда оно число, то пишется абсолютно так же
Потому что FLOAT конвертируется в DEC() не по тем правилам,
по которым VARCHAR() конвертируется в DEC()

А так?
select cast(cast('-2.057E+09' as float) as decimal (32,2))
26 мар 12, 17:47    [12316281]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
ne_ponjal
Guest
iap,

хотите сказать, что если пишу без кавычек,
select cast(-2.057E+09 as decimal(32,2))

то он его как FLOAT видит?
а для DECIMAL вообще запрещено задавать константу в виде ...e+10?
26 мар 12, 17:49    [12316303]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
ne_ponjal
iap,

хотите сказать, что если пишу без кавычек,
select cast(-2.057E+09 as decimal(32,2))

то он его как FLOAT видит?
а для DECIMAL вообще запрещено задавать константу в виде ...e+10?
Да
26 мар 12, 17:49    [12316304]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
ne_ponjal
то он его как FLOAT видит?
Проверьте сами.
select 
    SQL_VARIANT_PROPERTY ( cast('-2.057E+09' as sql_variant), 'BaseType')
    , SQL_VARIANT_PROPERTY ( cast(-2.057E+09 as sql_variant), 'BaseType')
26 мар 12, 17:51    [12316315]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
ne_ponjal
Guest
Ivan Durak,

а так канает, видимо так и есть, для FLOAT это допустимая запись, а для DECIMAL нет
26 мар 12, 17:51    [12316316]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при конвертации  [new]
ne_ponjal
Guest
Гавриленко Сергей Алексеевич,

ну вот, так оно и оказалось.
спасибо, что научили проверять
26 мар 12, 17:52    [12316322]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить