不要让我用ISNUMERIC()这个涵数,因为这个系统函数是有问题比如:“\”,还有一些符号,它都会认为是数字,当我转换的时候就出错了。求一个涵数,判段是否为数字 要求只能“12345”或”12.214“的才是数字,”只能有一个小数点的
还要考虐到全角问题,
我这有一个涵数,我不太懂,求大家按我的需求帮我改一下,谢谢了IF OBJECT_ID(N'dbo.isReallyNumeric', N'FN') IS NOT NULL
DROP FUNCTION dbo.isReallyNumeric;
GOCREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(128)
)
RETURNS BIT
BEGIN
set @num = LTRIM(RTRIM(@num))
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num)) DECLARE @pos INTEGER SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num)) RETURN CASE
WHEN PATINDEX('%[^0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
END
GO
还要考虐到全角问题,
我这有一个涵数,我不太懂,求大家按我的需求帮我改一下,谢谢了IF OBJECT_ID(N'dbo.isReallyNumeric', N'FN') IS NOT NULL
DROP FUNCTION dbo.isReallyNumeric;
GOCREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(128)
)
RETURNS BIT
BEGIN
set @num = LTRIM(RTRIM(@num))
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num)) DECLARE @pos INTEGER SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num)) RETURN CASE
WHEN PATINDEX('%[^0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
END
GO
--没觉得这个函数有问题呀
select isnumeric(\) as 不带引号
select isnumeric('\') as 带引号/*
不带引号
-----------
1(1 行受影响)带引号
-----------
0(1 行受影响)*/
还要考虐到全角问题,
-------------------------------------------为什么你说的这些问题,我用ISNUMERIC()都没有呢?
ISNUMERIC(23123/123) --1ISNUMERIC('23123%123') --0
ISNUMERIC(23123%123) --1
DROP FUNCTION dbo.isReallyNumeric;
GOCREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(128)
)
RETURNS BIT
BEGIN
set @num = LTRIM(RTRIM(@num))
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num)) DECLARE @pos int SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num)) RETURN CASE
WHEN PATINDEX('%[^0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
END
GO我试了半天,挺好的,只是DECLARE @pos int改了一下
DROP FUNCTION dbo.isReallyNumeric;
GOCREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(128)
)
RETURNS BIT
BEGIN
SET @num = LTRIM(RTRIM(@num))
IF LEFT(@num, 1)='-' COLLATE CHINESE_PRC_BIN
SET @num = SUBSTRING(@num, 2, LEN(@num))
DECLARE @pos INTEGER
SET @pos = 1+LEN(@num)- CHARINDEX('.' COLLATE CHINESE_PRC_BIN, REVERSE(@num))
RETURN
CASE
WHEN PATINDEX('%[^0-9.-]%' COLLATE CHINESE_PRC_BIN, @num)=0
AND @num COLLATE CHINESE_PRC_BIN NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND (((@pos=LEN(@num)+1) OR @pos=CHARINDEX('.' COLLATE CHINESE_PRC_BIN, @num)))
THEN 1
ELSE 0
END
END
GOSELECT dbo.isReallyNumeric('-12')
/*
-----
0(1 行受影响)
*/
SELECT dbo.isReallyNumeric('-231.12')
/*
-----
1(1 行受影响)
*/考虑全半角
DROP FUNCTION dbo.isReallyNumeric;
GOCREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(128)
)
RETURNS BIT
BEGIN
SET @num = LTRIM(RTRIM(@num))
IF LEFT(@num, 1)='-' COLLATE CHINESE_PRC_BIN
SET @num = SUBSTRING(@num, 2, LEN(@num))
DECLARE @pos INTEGER
SET @pos = 1+LEN(@num)- CHARINDEX('.' COLLATE CHINESE_PRC_BIN, REVERSE(@num))
RETURN
CASE
WHEN PATINDEX('%[^0-9.-]%' COLLATE CHINESE_PRC_BIN, @num)=0
AND @num COLLATE CHINESE_PRC_BIN NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%' COLLATE CHINESE_PRC_BIN --补充这里
AND (((@pos=LEN(@num)+1) OR @pos=CHARINDEX('.' COLLATE CHINESE_PRC_BIN, @num)))
THEN 1
ELSE 0
END
END
GOSELECT dbo.isReallyNumeric('-12')
/*
-----
0(1 行受影响)
*/
SELECT dbo.isReallyNumeric('-231.12')
/*
-----
0(1 行受影响)
*/SELECT dbo.isReallyNumeric('-231.12')
/*
-----
1(1 行受影响)
*/修改一下。
SELECT ISNUMERIC('$56') --1
SELECT ISNUMERIC('¥56') --1
SELECT ISNUMERIC('5E6') --1
SELECT ISNUMERIC('5,6,1') --1
SELECT string,
IsSystemNumeric = ISNUMERIC(string),
IsReallyNumeric = CASE
WHEN LEN(string) > 0
AND string COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
AND string COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
AND string COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
THEN 1 ELSE 0 END
FROM (
SELECT string = '123' UNION ALL
SELECT string = '.123' UNION ALL
SELECT string = '123.45' UNION ALL
SELECT string = '-123' UNION ALL
SELECT string = '-.123' UNION ALL
SELECT string = '-123.45' UNION ALL
SELECT string = '123.45.67' UNION ALL
SELECT string = '$123.00' UNION ALL
SELECT string = '5E6' UNION ALL
SELECT string = '5/6' UNION ALL
SELECT string = '5*6' UNION ALL
SELECT string = '5-6'
) tmp
select base_id, [31] as pkgcode,[84] as grossWeightunit,[189] as netWeightunit,[332] as specification,
[356] as shelfLife,[52] as tradeItemGroupIdentificationDescription,[132] as descriptionShort,
[51] as PackagingMaterialCompositionQuantity,[405] as Placeoforigin,
CASE WHEN ISNUMERIC([106])=1 THEN convert(numeric(18,4),[106]) ELSE 0 END as height,
CASE WHEN ISNUMERIC([101])=1 THEN convert(numeric(18,4),[101]) ELSE 0 END as width,
CASE WHEN ISNUMERIC([118])=1 THEN convert(numeric(18,4),[118]) ELSE 0 END as depth,
CASE WHEN ISNUMERIC([196])=1 THEN convert(numeric(18,4),[196]) ELSE 0 END as retailPriceOnTradeItem,
CASE WHEN ISNUMERIC([54])=1 THEN convert(numeric(18,4),[54]) ELSE 0 END as gross,
CASE WHEN ISNUMERIC([10])=1 THEN convert(numeric(18,4),[10]) ELSE 0 END as netWeight,
CASE WHEN isdate([325])=1 THEN convert(datetime,[325]) ELSE convert(datetime,'9999-12-31') END as DiscontinuedDate
FROM
(SELECT valu,att_id,base_id from tradeitembv ) P pivot ( max(valu) FOR att_id
IN ([106], [101],[118],[31],[54],[84],[10],[189],[196],[332],
[356],[132],[52],[88],[51],[325],[405]) ) AS pvt ISNUMERIC()这个涵数,只要我数据库里有\这样的就转不过去,不知道怎么解决,谢谢大家了
[356] as shelfLife,[52] as tradeItemGroupIdentificationDescription,[132] as descriptionShort,
[51] as PackagingMaterialCompositionQuantity,[405] as Placeoforigin,
CASE WHEN dbo.isReallyNumeric([106])=1 THEN convert(numeric(18,4),[106]) ELSE 0 END as height,
CASE WHEN dbo.isReallyNumeric([101])=1 THEN convert(numeric(18,4),[101]) ELSE 0 END as width,
CASE WHEN dbo.isReallyNumeric([118])=1 THEN convert(numeric(18,4),[118]) ELSE 0 END as depth,
CASE WHEN dbo.isReallyNumeric([196])=1 THEN convert(numeric(18,4),[196]) ELSE 0 END as retailPriceOnTradeItem,
CASE WHEN dbo.isReallyNumeric([54])=1 THEN convert(numeric(18,4),[54]) ELSE 0 END as gross,
CASE WHEN dbo.isReallyNumeric([10])=1 THEN convert(numeric(18,4),[10]) ELSE 0 END as netWeight,
CASE WHEN isdate([325])=1 THEN convert(datetime,[325]) ELSE convert(datetime,'9999-12-31') END as DiscontinuedDate
FROM
(SELECT valu,att_id,base_id from tradeitembv ) P pivot ( max(valu) FOR att_id
IN ([106], [101],[118],[31],[54],[84],[10],[189],[196],[332],
[356],[132],[52],[88],[51],[325],[405]) ) AS pvt
从数据类型 varchar 转换为 numeric 时出错。
我也碰到这种情况,郁闷死了啊,哪位高手来救命