如果字符不好截取的话.我可以一个一个的移.比如像这样查询一下 like '%500ml%'语法要怎么写?
create table tb(item_name varchar(30)) insert into tb select '12g旺仔牛奶' insert into tb select '500ml*15统一绿茶' insert into tb select '康师傅108g泡面' go declare @s varchar(10),@s1 varchar(10),@s2 varchar(10) set @s='12g' set @s1='500ml*15' set @s2='108g' /* 可以一个个设,然后运行,也可以一次弄多个 */ update tb set item_name= (case when CHARINDEX(@s,item_name)>0 then REPLACE(item_name,@s,'')+@s when CHARINDEX(@s1,item_name)>0 then REPLACE(item_name,@s1,'')+@s1 when CHARINDEX(@s2,item_name)>0 then REPLACE(item_name,@s2,'')+@s2 end) select * from tb /* item_name ------------------------------ 旺仔牛奶12g 统一绿茶500ml*15 康师傅泡面108g(3 行受影响)*/ go drop table tb
本帖最后由 roy_88 于 2011-10-28 07:53:15 编辑
use Tempdb GO if OBJECT_ID ( 'F_Str1' ) is not null drop function F_Str1 go /* 此函数缺点,字符长度越长性能越低。 */ create FUNCTION dbo . F_Str1 ( @Str NVARCHAR ( 4000 ), -- 需要处理的字符串 @Condition nvarchar ( 1000 ) -- 保留字符串如 :N'%[0-9a-zA-Z]%', 注意这里需要有 [] ) RETURNS NVARCHAR ( 4000 ) AS BEGIN if charindex ( '[^' , @Condition )=0 set @Condition = STUFF ( @Condition , charindex ( '[' , @Condition )+ 1 , 0 , '^' ) --这一段是为了与后面函数的条件统一 declare @i int set @i = PATINDEX ( @Condition , @Str ) WHILE @i > 0 SELECT @Str = STUFF ( @Str , @i , 1 , '' ), @i = PATINDEX ( @Condition , @Str ) RETURN @Str END go --> --> (Roy)生成測試數據
declare @t table([item_name] nvarchar(12)) Insert @t select N'12g旺仔牛奶' union all select N'500ml*15统一绿茶' union all select N'康师傅108g泡面' union all select N'来一桶泡面208g'
Select [item_name]= case when PATINDEX('%[^吖-座]%',[item_name])=1 then right([item_name],len([item_name])-PATINDEX('%[吖-座]%',[item_name])+1) when PATINDEX('%[^吖-座]%',reverse([item_name]))>1 then stuff([item_name],PATINDEX('%[^吖-座]%',[item_name]) ,len([item_name])-(patindex('%[^吖-座]%',reverse([item_name]))+PATINDEX('%[^吖-座]%',[item_name]))+2 ,'') else left([item_name],len([item_name])-PATINDEX('%[吖-座]%',reverse([item_name]))+1)end + case when PATINDEX('%[^吖-座]%',[item_name])=1 then LEFT([item_name],PATINDEX('%[吖-座]%',[item_name])-1) when PATINDEX('%[^吖-座]%',reverse([item_name]))>1 then substring([item_name],PATINDEX('%[^吖-座]%',[item_name]) ,len([item_name])-(patindex('%[^吖-座]%',reverse([item_name]))+PATINDEX('%[^吖-座]%',[item_name]))+2 ) else right([item_name],PATINDEX('%[吖-座]%',reverse([item_name]))-1)end from @t/* 旺仔牛奶12g 统一绿茶500ml*15 康师傅泡面108g 来一桶泡面208g */
insert into tb select '12g旺仔牛奶'
insert into tb select '500ml*15统一绿茶'
insert into tb select '康师傅108g泡面'
go
declare @s varchar(10),@s1 varchar(10),@s2 varchar(10)
set @s='12g'
set @s1='500ml*15'
set @s2='108g'
/*
可以一个个设,然后运行,也可以一次弄多个
*/
update tb set item_name=
(case when CHARINDEX(@s,item_name)>0 then REPLACE(item_name,@s,'')+@s
when CHARINDEX(@s1,item_name)>0 then REPLACE(item_name,@s1,'')+@s1
when CHARINDEX(@s2,item_name)>0 then REPLACE(item_name,@s2,'')+@s2
end)
select * from tb
/*
item_name
------------------------------
旺仔牛奶12g
统一绿茶500ml*15
康师傅泡面108g(3 行受影响)*/
go
drop table tb
GO
if OBJECT_ID ( 'F_Str1' ) is not null
drop function F_Str1
go
/*
此函数缺点,字符长度越长性能越低。
*/
create FUNCTION dbo . F_Str1 (
@Str NVARCHAR ( 4000 ), -- 需要处理的字符串
@Condition nvarchar ( 1000 ) -- 保留字符串如 :N'%[0-9a-zA-Z]%', 注意这里需要有 []
)
RETURNS NVARCHAR ( 4000 )
AS
BEGIN
if charindex ( '[^' , @Condition )=0
set @Condition = STUFF ( @Condition , charindex ( '[' , @Condition )+ 1 , 0 , '^' ) --这一段是为了与后面函数的条件统一
declare @i int
set @i = PATINDEX ( @Condition , @Str )
WHILE @i > 0
SELECT @Str = STUFF ( @Str , @i , 1 , '' ), @i = PATINDEX ( @Condition , @Str )
RETURN @Str
END
go
--> --> (Roy)生成測試數據
declare @t table([item_name] nvarchar(12))
Insert @t
select N'12g旺仔牛奶' union all
select N'500ml*15统一绿茶' union all
select N'康师傅108g泡面' union all
select N'来一桶泡面208g'
Select
[item_name]=
case when PATINDEX('%[^吖-座]%',[item_name])=1 then right([item_name],len([item_name])-PATINDEX('%[吖-座]%',[item_name])+1)
when PATINDEX('%[^吖-座]%',reverse([item_name]))>1
then stuff([item_name],PATINDEX('%[^吖-座]%',[item_name])
,len([item_name])-(patindex('%[^吖-座]%',reverse([item_name]))+PATINDEX('%[^吖-座]%',[item_name]))+2 ,'')
else left([item_name],len([item_name])-PATINDEX('%[吖-座]%',reverse([item_name]))+1)end
+
case when PATINDEX('%[^吖-座]%',[item_name])=1 then LEFT([item_name],PATINDEX('%[吖-座]%',[item_name])-1)
when PATINDEX('%[^吖-座]%',reverse([item_name]))>1
then substring([item_name],PATINDEX('%[^吖-座]%',[item_name])
,len([item_name])-(patindex('%[^吖-座]%',reverse([item_name]))+PATINDEX('%[^吖-座]%',[item_name]))+2 )
else right([item_name],PATINDEX('%[吖-座]%',reverse([item_name]))-1)end
from @t/*
旺仔牛奶12g
统一绿茶500ml*15
康师傅泡面108g
来一桶泡面208g
*/