SQL语句怎么写
y2-0.75-4
y2-0.55-2(660v)
y-132m-0.37-6要求:如果有括号就去除括号与括号中的内容,如果有3个“-”就去除前两个“-”中的内容与其中一个“-”
变成y2-0.75-4
y2-0.55-2
y2-0.37-6
y2-0.75-4
y2-0.55-2(660v)
y-132m-0.37-6要求:如果有括号就去除括号与括号中的内容,如果有3个“-”就去除前两个“-”中的内容与其中一个“-”
变成y2-0.75-4
y2-0.55-2
y2-0.37-6
as(select 'y2-0.75-4' as num union
select 'y2-0.55-2(660v)' union
select 'y-132m-0.37-6')select case when CHARINDEX( '(',num) >0
then LEFT(num,charindex('(',num) - 1)
else
case when LEN(num) - len(REPLACE(num,'-',''))=3
then
LEFT(num,charindex('-',num))+substring(SUBSTRING(num,charindex('-',num)+1,LEN(num)),charindex('-',SUBSTRING(num,charindex('-',num)+1,LEN(num)))+1,LEN(SUBSTRING(num,charindex('-',num)+1,LEN(num))))
else
num
end
end
from TT/*
y-0.37-6
y2-0.55-2
y2-0.75-4*/
--創建函數
create function f_firstposition(@Str varchar(8000),@StrSep varchar(10),@AppPos int)
returns int
begin
declare @i int
declare @ii int
set @Str=rtrim(ltrim(@Str))
set @i=1
select @ii=charindex(@StrSep,@Str)
if @i=@AppPos
return @ii
else
while @AppPos>@i
begin
if charindex(@StrSep,right(@Str,len(@Str)-@ii))<>0
select @ii=charindex(@StrSep,right(@Str,len(@Str)-@ii))+@ii
else
set @ii=0
set @i=@i+1
end return @ii
END
go;--執行查詢
WITH a1 (cstr) AS
(
SELECT 'y2-0.75-4' UNION ALL
SELECT 'y2-0.55-2(660v)' UNION ALL
SELECT 'y2-132m-0.37-6'
)
,a2 AS
(
SELECT
CASE
WHEN dbo.f_firstposition(cstr,'-',3)>0
THEN STUFF(cstr,dbo.f_firstposition(cstr,'-',1)+1,dbo.f_firstposition(cstr,'-',2)-dbo.f_firstposition(cstr,'-',1),'')
ELSE cstr
END cstr
FROM a1
)
SELECT
CASE
WHEN dbo.f_firstposition(cstr,'(',1)>0
THEN STUFF(cstr,dbo.f_firstposition(cstr,'(',1),dbo.f_firstposition(cstr,')',1)-dbo.f_firstposition(cstr,'(',1)+1,'')
ELSE cstr
END cstr
FROM a2