--> 测试数据: [t] if object_id('[t]') is not null drop table [t] create table [t] (num varchar(8)) insert into [t] select '213' union all select '外撒法34' union all select '34a' union all select '44' union all select '5'select * from [t] where isnumeric(num)=1 and num>10
--比較的時候加上條件 where isnumeric(列)=1
字串中含有字符,直接转成numeric肯定是不行的。
--提取数字的例子 use tempdb gocreate function f_test(@value varchar(max)) returns int as begin declare @s varchar(max),@tv char(1) declare @len int,@flag int set @s = '' set @tv = '' select @len = len(@value) set @flag = 1 while @flag <= @len begin set @tv = substring(@value,@flag,1)
if isnumeric(@tv) = 1 set @s = @s + @tv set @flag = @flag + 1 end return(convert(int,@s)) end godeclare @s varchar(max) set @s = '啊34不6' select dbo.f_test(@s) godrop function f_test go
if object_id('[t]') is not null drop table [t] create table [t] (num varchar(8)) insert into [t] select '213' union all select '外撒法34' union all select '34a' union all select '44' union all select '5' --1問,是將字符裡的數字提取出來 如'外撒法34'-->34 ,比較 --2還是只比較是數字的 --如果是1則可以用函數提取,如果2則可以用isnumeric過濾出數值的 --提取数字 IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL DROP FUNCTION DBO.GET_NUMBER2 GO CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^0-9]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'') END RETURN @S END GO --测试 PRINT DBO.GET_NUMBER('呵呵ABC123ABC') GO
wufeng4552 和CN_SQL 两个大哥写的东西,小弟没看懂啊,至于后来说的isnumeric(列名)=1这个,关键是我这一列还要进行比较,我把sql贴出来吧,这是一个触发器,其中result就是nvarchar(50)的列,和他比较的value_Min和value_Max是numeric(8,2)类型的.set ANSI_NULLS ON set QUOTED_IDENTIFIER ON goALTER TRIGGER [mytrigger] ON [dbo].[TP_Plan_Detail] WITH EXECUTE AS CALLER FOR INSERT --, UPDATE AS BEGINinsert pitemp (CPName,InstrumentName,CIName,CIType,CIValue,CIMin,CIMax,CITime,PICode) SELECT dbo.TP_Plan_Detail.CI_ID, dbo.TB_CIO.Value_Min, dbo.TB_CIO.Value_Max, dbo.TP_Plan_Detail.Result, dbo.TB_CI.PICodeFROM dbo.TP_Plan_Detail INNER JOIN dbo.TB_CIO ON dbo.TP_Plan_Detail.CI_ID = dbo.TB_CIO.CI_ID INNER JOIN dbo.TB_CI ON dbo.TP_Plan_Detail.CI_ID = dbo.TB_CI.CI_IDwhere convert(TP_Plan_Detail.result,numeric(8,2))<value_Min and TB_CI.PiCode is not null and TP_Plan_Detail.Description is null or convert(TP_Plan_Detail.result,numeric(8,2))>value_Max and TB_CI.PiCode is not null and TP_Plan_Detail.Description is null -- --update TP_Plan_Detail set Description=1 from TP_Plan_Detail -- join TB_CIO on TP_Plan_Detail.CI_ID=TB_CIO.CI_ID -- where result<value_Min or result>value_Max END
--> 测试数据: [t]
if object_id('[t]') is not null drop table [t]
create table [t] (num varchar(8))
insert into [t]
select '213' union all
select '外撒法34' union all
select '34a' union all
select '44' union all
select '5'select * from [t] where isnumeric(num)=1 and num>10
where isnumeric(列)=1
--提取数字的例子
use tempdb
gocreate function f_test(@value varchar(max))
returns int
as
begin
declare @s varchar(max),@tv char(1)
declare @len int,@flag int set @s = ''
set @tv = '' select @len = len(@value)
set @flag = 1
while @flag <= @len
begin
set @tv = substring(@value,@flag,1)
if isnumeric(@tv) = 1
set @s = @s + @tv set @flag = @flag + 1
end return(convert(int,@s))
end
godeclare @s varchar(max)
set @s = '啊34不6'
select dbo.f_test(@s)
godrop function f_test
go
create table [t] (num varchar(8))
insert into [t]
select '213' union all
select '外撒法34' union all
select '34a' union all
select '44' union all
select '5'
--1問,是將字符裡的數字提取出來 如'外撒法34'-->34 ,比較
--2還是只比較是數字的
--如果是1則可以用函數提取,如果2則可以用isnumeric過濾出數值的
--提取数字
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
而是"啊23.5不","就343.78的"这样的情况,那就需要调整下函数判断了
把非NUERICE格式的字符去掉,然后才能比较.
大体思路反正是这样.
set QUOTED_IDENTIFIER ON
goALTER TRIGGER [mytrigger] ON [dbo].[TP_Plan_Detail]
WITH EXECUTE AS CALLER
FOR INSERT
--, UPDATE
AS
BEGINinsert pitemp (CPName,InstrumentName,CIName,CIType,CIValue,CIMin,CIMax,CITime,PICode)
SELECT dbo.TP_Plan_Detail.CI_ID,
dbo.TB_CIO.Value_Min,
dbo.TB_CIO.Value_Max,
dbo.TP_Plan_Detail.Result,
dbo.TB_CI.PICodeFROM dbo.TP_Plan_Detail
INNER JOIN dbo.TB_CIO ON dbo.TP_Plan_Detail.CI_ID = dbo.TB_CIO.CI_ID
INNER JOIN dbo.TB_CI ON dbo.TP_Plan_Detail.CI_ID = dbo.TB_CI.CI_IDwhere convert(TP_Plan_Detail.result,numeric(8,2))<value_Min
and TB_CI.PiCode is not null
and TP_Plan_Detail.Description is null
or convert(TP_Plan_Detail.result,numeric(8,2))>value_Max
and TB_CI.PiCode is not null
and TP_Plan_Detail.Description is null
--
--update TP_Plan_Detail set Description=1 from TP_Plan_Detail
-- join TB_CIO on TP_Plan_Detail.CI_ID=TB_CIO.CI_ID
-- where result<value_Min or result>value_Max
END
这个等于1起到什么作用,格式化的作用吗?
isnumeric(列名)=1 就是说这一列是数字