求助：SQL字符串问题（在线等）

wms05128 2010-09-16 10:22:27

有一字符串如下所示：‘ABC*BCD+A-MPP’,我要得到其中的ABC,BCD,A,MPP，意思就是剔除其中的+，-，*号，请问怎么实现

...全文

110 点赞收藏 8

写回复

8 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

kadboy 2010-09-16

declare @str varchar(1000)
set @str='ABC*BCD+A-MPP'

declare @Pos int,@Begin int
set @Pos=1
set @Begin=1
while(@Pos<=len(@str))
begin
if(charindex(substring(@str,@Pos,1),'*+-')>0)
begin
print substring(@str,@Begin,@Pos-@Begin)
set @Begin=@Pos+1
end
else if(@Pos=len(@str))
begin
print substring(@str,@Begin,@Pos-@Begin+1)
end
set @Pos=@Pos+1
end

回复赞

pcbxhm 2010-09-16

厉害。。支持一下

回复赞

zsh0809 2010-09-16

5# 能实现你的需要

回复赞

zsh0809 2010-09-16

/*

名称：fn_split函数.

功能：实现字符串分隔功能的函数

*/

create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))

returns @temp table (a varchar(200))

as 

begin

  declare @i int

  set @inputstr = rtrim(ltrim(@inputstr))

  set @i = charindex(@seprator , @inputstr)

  while @i >= 1

  begin

    insert @temp values(left(@inputstr , @i - 1))

    set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)

    set @i = charindex(@seprator , @inputstr)

  end

  if @inputstr <> '\'

  insert @temp values(@inputstr)

  return 

end

go



--调用



DECLARE @str VARCHAR(100)

SET @str='ABC*BCD+A-MPP'

SET @str= replace(replace(REPLACE(@str,'*',','),'+',','),'-',',')



select * from

dbo.fn_split(@str,',')



a

------

ABC

BCD

A

MPP



(4 row(s) affected)

回复赞

wms05128 2010-09-16

[Quote=引用 2 楼 ws_hgo 的回复:]

SQL code
select replace(replace(replace('ABC*BCD+A-MPP','-',''),'*',''),'+','')
----------------------------------------------------------------------------------------------------------------------……
[/Quote]
能写个通用的替换函数吗？

回复赞

wms05128 2010-09-16

[Quote=引用 1 楼 ws_hgo 的回复:]

SQL code
select replace(replace(replace('ABC*BCD+A-MPP','-',''),'*',''),'+','')
----------------------------------------------------------------------------------------------------------------------……
[/Quote]

如果字符串为IF(ABC=1,0,ABC*2)这样子呢？？我也要提取其中的ABC出来

回复赞

ws_hgo 2010-09-16

select replace(replace(replace('ABC*BCD+A-MPP','-',''),'*',''),'+','')

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ABCBCDAMPP



(1 行受影响)

回复赞

ws_hgo 2010-09-16

select replace(replace(replace('ABC*BCD+A-MPP','-',''),'*',''),'+','')

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ABCBCDAMPP



(1 行受影响)

回复赞