А я только что багу в MS SQL нашел

[Dmitry67]

select isnumeric('12,34')
select convert(float, '12,34')

-----------
1

(1 ligne(s) affectée(s))

Serveur : Msg 8114, Niveau 16, État 5, Ligne 2
Erreur de conversion du type de données varchar en float.

BOL:
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.

Примечание: я во Франции, и во float дробная часть отделяется от целой запятой а не точкой. SQL server не должен зависеть от locale, но видимо в isnumeric это не так

Кто может попробовать на английской машине ?

Да,
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )

[Seryi]

то же самое.

[tengiz]

Это ожидаемое поведение - запятая в Вашем примере (используемая как косметический разделитель разрядов) работает только для money/smallmoney, то же относится к '$' в качестве первого символа:

select isnumeric('12,34')
select convert (money, '12,34')
go
select isnumeric('$12,34')
select convert (money, '$12,34')
go
select isnumeric('$12,34')
select convert (float, '$12,34')

[Dmitry67]

Нет, в BOL сказано floating point number
Согласитесь, что глядя на фразу BOL данное поведение достаточно confusing...

[Dmitry67]

Кстати, еще давно про одну штуку хотел посетовать
Чисто синтаксические ограничения не дают возможность использовать table variables для self-joined update:

update #TAB set ...
from #TAB, #TAB as PARENT
where #PARENT.ID=#TAB.PARENT

чисто синтаксически не переписать с использованием @TAB

[tengiz]

Нет, в BOL сказано floating point number
Согласитесь, что глядя на фразу BOL данное поведение достаточно confusing...

В BOL сказано: evaluates to a valid integer, floating point number, money or decimal type. Что тут confusing? Вам говорят, что преобразование возможно в один из этих типов. Но не во все сразу. Попробуйте, например, вот это и сразу станет ясно:

select isnumeric('12.34')
select convert (int, '12.34')

[tengiz]

Кстати, еще давно про одну штуку хотел посетовать
Чисто синтаксические ограничения не дают возможность использовать table variables для self-joined update..

Используйте алиасы:

Code: Select all

declare @tab table
(
  id     int primary key,
  parent int,
  a      int,
  b      int
)

update child
set    child.a = parent.b 
from   @tab child, @tab parent
where  parent.id = child.parent

[Dmitry67]

про алиасы то я знаю
мне кажется мне удавалось запутать SQL и он говорил что таблица tab 'is ambigious'
Я попытаюсь воспроизвести

[zVlad]

In DB2, there is no function like "isnumeric" but there is a SQLCODE=-420 to show that numeric value is not correct.
DB2 has also set up parameter to say which sign you want to use as a decimal pointer: PERIOD, or COMMA. Regardless what value PERIOD, or COMMA is set up, tests show:

1.
SELECT,DECIMAL('12,34') FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+-------
12.
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

2.
SELECT DECIMAL('12.34') FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+-------
12.
DSNE610I NUMBER OF ROWS DISPLAYED IS 1


3.
SELECT DECIMAL('12A34') FROM SYSIBM.SYSDUMMY1;
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -420, ERROR: THE VALUE OF A CHARACTER STRING ARGUMENT WAS
NOT ACCEPTABLE TO THE DECIMAL FUNCTION


4.
SELECT FLOAT('12.34') FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+-------
+0.1234000000000000E+02
DSNE610I NUMBER OF ROWS DISPLAYED IS 1


5.
SELECT FLOAT('12,34') FROM SYSIBM.SYSDUMMY1;
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -420, ERROR: THE VALUE OF A CHARACTER STRING ARGUMENT WAS
NOT ACCEPTABLE TO THE FLOAT FUNCTION


6.
SELECT FLOAT(DECIMAL('12,34', 5, 2)) FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+---------+-
+0.1234000000000000E+02
DSNE610I NUMBER OF ROWS DISPLAYED IS 1


7.
SELECT FLOAT(DECIMAL('12.34', 5, 2)) FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+--------
+0.1234000000000000E+02
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

[Dmitry67]

Это понятно, но функция такая полезна
Думаю лучше бы сделать функцию IsAcceptable(type, string) вместо ISDate, IsNumeric итд...

zVlad: выдача ошибки не решает проблему
У Вас есть таблица где значения допустим float хранятся в строке, а еще есть слова и мусор. Мусор конвертируется в NULL, а то что можно конвертировать во float - во float.
В MS SQL это делается одним оператором. В DB2 придется писать stored procedure, где крутить цикл по каждой записи проверять ошибку ?

[testuser]

Это понятно, но функция такая полезна
Думаю лучше бы сделать функцию IsAcceptable(type, string) вместо ISDate, IsNumeric итд...

если написать свою вспомогательную функцию IsAcceptable(type, string) займет больша получаса, это позор (это я как твой бывший ученик говорю )

[Dmitry67]

Это понятно, но функция такая полезна
Думаю лучше бы сделать функцию IsAcceptable(type, string) вместо ISDate, IsNumeric итд...

если написать свою вспомогательную функцию IsAcceptable(type, string) займет больша получаса, это позор (это я как твой бывший ученик говорю )

Привет массачусетсчене. Все не так просто, особенно если учесть проблемы переполнения. К тому же все таки встроенные функции работают куда быстрее чем пользовательские

[zVlad]

Это понятно, но функция такая полезна
Думаю лучше бы сделать функцию IsAcceptable(type, string) вместо ISDate, IsNumeric итд...

zVlad: выдача ошибки не решает проблему
У Вас есть таблица где значения допустим float хранятся в строке, а еще есть слова и мусор. Мусор конвертируется в NULL, а то что можно конвертировать во float - во float.
В MS SQL это делается одним оператором. В DB2 придется писать stored procedure, где крутить цикл по каждой записи проверять ошибку ?

Дима, Вы меня в очередной раз огорошили. Что значит " float хранятся в строке, а еще есть слова и мусор."
Это, например, символьный столбец с данными: " бла-бла-бла 0.1234Е+02 бла-бла-бла"?
А что делается одним оператором в MS SQL? А разве в этом случае не надо будет "...крутить цикл по каждой записи" результата "одного оператора" MS SQL?

[Dmitry67]

Дима, Вы меня в очередной раз огорошили. Что значит " float хранятся в строке, а еще есть слова и мусор."
Это, например, символьный столбец с данными: " бла-бла-бла 0.1234Е+02 бла-бла-бла"?
А что делается одним оператором в MS SQL? А разве в этом случае не надо будет "...крутить цикл по каждой записи" результата "одного оператора" MS SQL?

Такова задача
Так получилось что float исторически хранилось в строке, например так
'blah blah blah/10ml'
'other thing 15kg'

итд
Ваша задача преобразовать это в нормальный формат, где float хранистя как float
Вы парзите строку, вырезаете подстроку '15', '10', и теперь Ваша задача сконвартировать во float, но надо проверить а можно ли это значение сконвертировать без ошибки
Для такой цели и применяется фенкции типа IsNumeric

В MS SQL разумеется циклов крутить не надо. Вот упрощенные пример, в TAB strvalue уже выделенная подстрока

Code: Select all

create table GOODTABLE (pk int, value float)
create table STUPIDTABLE (pk int, value varchar(255))

insert into GOODTABLE (pk,value)
  select pk,
    case when Isnumeric(value)>0 then convert(float,value)
     else NULL end
  from STUPIDTABLE

[zVlad]

Дима, Вы меня в очередной раз огорошили. Что значит " float хранятся в строке, а еще есть слова и мусор."
Это, например, символьный столбец с данными: " бла-бла-бла 0.1234Е+02 бла-бла-бла"?
А что делается одним оператором в MS SQL? А разве в этом случае не надо будет "...крутить цикл по каждой записи" результата "одного оператора" MS SQL?

Такова задача
Так получилось что float исторически хранилось в строке, например так
'blah blah blah/10ml'
'other thing 15kg'

итд
Ваша задача преобразовать это в нормальный формат, где float хранистя как float
Вы парзите строку, вырезаете подстроку '15', '10', и теперь Ваша задача сконвартировать во float, но надо проверить а можно ли это значение сконвертировать без ошибки
Для такой цели и применяется фенкции типа IsNumeric

В MS SQL разумеется циклов крутить не надо. Вот упрощенные пример, в TAB strvalue уже выделенная подстрока

Code: Select all

create table GOODTABLE (pk int, value float)
create table STUPIDTABLE (pk int, value varchar(255))

insert into GOODTABLE (pk,value)
  select pk,
    case when Isnumeric(value)>0 then convert(float,value)
     else NULL end
  from STUPIDTABLE

There is something wrong with "..в TAB strvalue уже выделенная подстрока". I don't see strvalue in your example.
Anyway I understand what you meant. Therefore my next question is why don't verify extracted float value for data type during parsing? I would do something like that. For example, in REXX, which I would use for parsing, there is function DATATYPE to verify type of value.
Moreover, if you verify it during parsing you have opportunity to fix some common problem, say, replace ‘,’ with ‘.’.

[Dmitry67]

Потому что парзинг строки в MS SQL я тоже делаю без циклов а с помощью вложенных inline view и case/when/then/else/end. Таким образом довольно сложный синтаксический разбор делается без курсоров и циклов, и значит может работать значительно быстрее как минимум по двух причинам.

[chekur13]

про алиасы то я знаю
мне кажется мне удавалось запутать SQL и он говорил что таблица tab 'is ambigious'
Я попытаюсь воспроизвести

declare @tab table
(
id int primary key,
parent int,
a int,
b int
)

update @tab
set a = parent.b
from @tab child, @tab parent
where parent.id = child.parent

Server: Msg 8154, Level 16, State 1, Line 9
The table '@tab' is ambiguous.

[tengiz]

Server: Msg 8154, Level 16, State 1, Line 9
The table '@tab' is ambiguous.

Code: Select all

begin tran

create table tab
( 
  id     int primary key, 
  parent int, 
  a      int, 
  b      int 
) 

update tab 
set    child.a = parent.b 
from   tab child, tab parent 
where  parent.id = child.parent

rollback tran

Msg 1032, Level 15, State 1, Line 12
Cannot use the column prefix 'child'. This must match the object in the UPDATE clause 'tab'.

Здесь в любом случае проблема с неоднозначностью в самом тексте запроса, хоть с табличной переменной, хоть с таблицей. Для табличной переменной просто другое сообщение об ошибке вылезает.