select * from tb where patindex('%DN[1-9][0-9][0-9]%',标准描述)>0
--如果数据格式有变化会出错 create function fn_isUp( @str varchar(3000), @str1 varchar(30), @Num int ) returns bit as begin declare @r bit declare @i int set @r=0 if charindex('\'+@str1,@str)<=0 return 0 if charindex('\',@str,charindex('\'+@str1,@str)+1)<=0 return 0 set @i=cast(substring(@str,charindex('\'+@str1,@str)+len(@str1)+1,charindex('\',@str,charindex('\'+@str1,@str)+1)-charindex('\'+@str1,@str)-len(@str1)-1) as int) if @i>@Num set @r=1 return @r end go --调用 select * from 你的表名 where dbo.fn_isUp(字段名,'DN',100)=1
select * from tb where patindex('%DN[1-9][0-9][0-9]%',标准描述)>0 函数patindex()挺不错的啊!
口径大于DN100 是个什么概念?怎么有DN50也用红色标记出来了?
CREATE TABLE T ( ID INT IDENTITY(1,1), 标准描述 VARCHAR(100) )INSERT INTO T SELECT '高压闸阀\Z941H-160C\DN50\GB12234' UNION ALL SELECT '高压闸阀\Z941H-160C\DN500\GB12234' UNION ALL SELECT '高压闸阀\Z941H-200C\DN200\GB12234' UNION ALL SELECT '高压闸阀\Z941H-160C\DN65\GB12234' UNION ALL SELECT '高压闸阀\Z941W-100R\DN350\GB12234' UNION ALL SELECT '高压闸阀\Z941W-100R\DN50\GB12234' UNION ALL SELECT '高压闸阀\Z941W-160R\DN50\GB12234' SELECT * FROM T WHERE CAST(STUFF( PARSENAME( --PARSENAME为一系统函数,是用来取IP地址段的,但是缺点是段内字符不能有特殊符号 REPLACE( REPLACE( REPLACE(标准描述,'\','.' ),'','' ),'','' ),2), 1, PATINDEX('%[0-9]%', PARSENAME( REPLACE( REPLACE( REPLACE(标准描述,'\','.' ),'','' ),'','' ), 2))-1, '') AS INT)>100 DROP TABLE TID 标准描述 ----------- ---------------------------------- 2 高压闸阀\Z941H-160C\DN500\GB12234 3 高压闸阀\Z941H-200C\DN200\GB12234 5 高压闸阀\Z941W-100R\DN350\GB12234(所影响的行数为 3 行)
create table tb(标准描述 varchar(50)) insert into tb values('高压闸阀\Z941H-160C\DN50\GB12234') insert into tb values('高压闸阀\Z941H-160C\DN500\GB12234') insert into tb values('高压闸阀\Z941H-200C\DN200\GB12234') insert into tb values('高压闸阀\Z941H-160C\DN65\GB12234') insert into tb values('高压闸阀\Z941W-100R\DN350\GB12234') insert into tb values('高压闸阀\Z941W-100R\DN50\GB12234') insert into tb values('高压闸阀\Z941W-160R\DN50\GB12234') goselect 标准描述 from ( select *,col = substring(标准描述,charindex('',标准描述) + 5 , charindex('',标准描述,charindex('[b]',标准描述)) - charindex('[b]',标准描述) - 5) from tb where charindex('[b]',标准描述) > 0 union all select *,col = substring(标准描述,charindex('DN',标准描述) + 2 , charindex('\',标准描述,charindex('DN',标准描述)) - charindex('DN',标准描述) - 2) from tb where charindex('DN',标准描述) > 0 and charindex('[b]',标准描述) <= 0 ) t where cast(col as int) > 100 drop table tb/* 标准描述 -------------------------------------------------- 高压闸阀\Z941H-160C\DN500\GB12234 高压闸阀\Z941H-200C\DN200\GB12234 高压闸阀\Z941W-100R\DN350\GB12234(所影响的行数为 3 行) */
何必这么麻烦,直接用patindexcreate table tb(标准描述 varchar(100)) insert into tb values('高压闸阀\Z941H-160C\DN50\GB12234') insert into tb values('高压闸阀\Z941H-160C\DN500\GB12234') insert into tb values('高压闸阀\Z941H-200C\DN200\GB12234') insert into tb values('高压闸阀\Z941H-160C\DN65\GB12234') insert into tb values('高压闸阀\Z941W-100R\DN350\GB12234') insert into tb values('高压闸阀\Z941W-100R\DN50\GB12234') insert into tb values('高压闸阀\Z941W-160R\DN50\GB12234') goselect * from tb where patindex('%DN[1-9][0-9][0-9]%',标准描述)>0drop table tb/* 标准描述 ---------------------------------------------------------------------------------------------------- 高压闸阀\Z941H-160C\DN500\GB12234 高压闸阀\Z941H-200C\DN200\GB12234 高压闸阀\Z941W-100R\DN350\GB12234(3 row(s) affected) */
where patindex('%DN[1-9][0-9][0-9]%',标准描述)>0
create function fn_isUp(
@str varchar(3000),
@str1 varchar(30),
@Num int
)
returns bit
as
begin
declare @r bit
declare @i int
set @r=0
if charindex('\'+@str1,@str)<=0 return 0
if charindex('\',@str,charindex('\'+@str1,@str)+1)<=0 return 0
set @i=cast(substring(@str,charindex('\'+@str1,@str)+len(@str1)+1,charindex('\',@str,charindex('\'+@str1,@str)+1)-charindex('\'+@str1,@str)-len(@str1)-1) as int)
if @i>@Num set @r=1
return @r
end
go
--调用
select * from 你的表名
where dbo.fn_isUp(字段名,'DN',100)=1
where patindex('%DN[1-9][0-9][0-9]%',标准描述)>0
函数patindex()挺不错的啊!
CREATE TABLE T
(
ID INT IDENTITY(1,1),
标准描述 VARCHAR(100)
)INSERT INTO T
SELECT '高压闸阀\Z941H-160C\DN50\GB12234' UNION ALL
SELECT '高压闸阀\Z941H-160C\DN500\GB12234' UNION ALL
SELECT '高压闸阀\Z941H-200C\DN200\GB12234' UNION ALL
SELECT '高压闸阀\Z941H-160C\DN65\GB12234' UNION ALL
SELECT '高压闸阀\Z941W-100R\DN350\GB12234' UNION ALL
SELECT '高压闸阀\Z941W-100R\DN50\GB12234' UNION ALL
SELECT '高压闸阀\Z941W-160R\DN50\GB12234'
SELECT *
FROM T
WHERE
CAST(STUFF(
PARSENAME( --PARSENAME为一系统函数,是用来取IP地址段的,但是缺点是段内字符不能有特殊符号
REPLACE(
REPLACE(
REPLACE(标准描述,'\','.'
),'',''
),'',''
),2),
1,
PATINDEX('%[0-9]%',
PARSENAME(
REPLACE(
REPLACE(
REPLACE(标准描述,'\','.'
),'',''
),'',''
),
2))-1,
'') AS INT)>100 DROP TABLE TID 标准描述
----------- ----------------------------------
2 高压闸阀\Z941H-160C\DN500\GB12234
3 高压闸阀\Z941H-200C\DN200\GB12234
5 高压闸阀\Z941W-100R\DN350\GB12234(所影响的行数为 3 行)
insert into tb values('高压闸阀\Z941H-160C\DN50\GB12234')
insert into tb values('高压闸阀\Z941H-160C\DN500\GB12234')
insert into tb values('高压闸阀\Z941H-200C\DN200\GB12234')
insert into tb values('高压闸阀\Z941H-160C\DN65\GB12234')
insert into tb values('高压闸阀\Z941W-100R\DN350\GB12234')
insert into tb values('高压闸阀\Z941W-100R\DN50\GB12234')
insert into tb values('高压闸阀\Z941W-160R\DN50\GB12234')
goselect 标准描述 from
(
select *,col = substring(标准描述,charindex('',标准描述) + 5 , charindex('',标准描述,charindex('[b]',标准描述)) - charindex('[b]',标准描述) - 5) from tb where charindex('[b]',标准描述) > 0
union all
select *,col = substring(标准描述,charindex('DN',标准描述) + 2 , charindex('\',标准描述,charindex('DN',标准描述)) - charindex('DN',标准描述) - 2) from tb where charindex('DN',标准描述) > 0 and charindex('[b]',标准描述) <= 0
) t
where cast(col as int) > 100
drop table tb/*
标准描述
--------------------------------------------------
高压闸阀\Z941H-160C\DN500\GB12234
高压闸阀\Z941H-200C\DN200\GB12234
高压闸阀\Z941W-100R\DN350\GB12234(所影响的行数为 3 行)
*/
insert into tb values('高压闸阀\Z941H-160C\DN50\GB12234')
insert into tb values('高压闸阀\Z941H-160C\DN500\GB12234')
insert into tb values('高压闸阀\Z941H-200C\DN200\GB12234')
insert into tb values('高压闸阀\Z941H-160C\DN65\GB12234')
insert into tb values('高压闸阀\Z941W-100R\DN350\GB12234')
insert into tb values('高压闸阀\Z941W-100R\DN50\GB12234')
insert into tb values('高压闸阀\Z941W-160R\DN50\GB12234')
goselect * from tb
where patindex('%DN[1-9][0-9][0-9]%',标准描述)>0drop table tb/*
标准描述
----------------------------------------------------------------------------------------------------
高压闸阀\Z941H-160C\DN500\GB12234
高压闸阀\Z941H-200C\DN200\GB12234
高压闸阀\Z941W-100R\DN350\GB12234(3 row(s) affected)
*/