SQL2000，删除指定字段值的第三个字符及后面的字符！谢谢

[FCWTR]

SQL2000

表：AAA
字段：aaa
aaa数值：A01-06-B05-SD
A1-7-B3-om
。。。
删除第三个“-及后面的数”
为：A01-06-B05
A1-7-B3

谢谢

[顺势而为1]

试试这个

if not object_id(N'Tempdb..#Tmp_Data') is null
    drop table #Tmp_Data
Go

Create table #Tmp_Data(myStr nvarchar(100))

Insert #Tmp_Data
select N'A01-06-B05-SD' union all
select N'A1-7-B3-om'
Go


Select myStr,ThreeC,Substring(myStr,1,Charindex(ThreeC,myStr)-2)
From (
			Select myStr,xmlStr.value('(/r/n)[4]','varchar(10)') as ThreeC
			From (
					Select myStr,convert(xml,'<r><n>'+replace(myStr,'-','</n><n>')+'</n></r>') as xmlStr From #Tmp_Data
				) a
	  ) a

[leo_lesley]

sql2000  可以试试用下面的自定义函数。

--测试数据
if not object_id(N'tA') is null
    drop table tA
Go
Create table tA([aaa] nvarchar(33))
Insert tA
select N'A01-06-B05-SD' union all
select N'A1-7-B3-om' union all
select N'B3-om' union all
select N'om' union all
select N'A1-7-B3-om-lio' union all
select N'A1-7-B3-om-yuuy-y6-656' 
Go

--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000),      --包含多个数据项的字符串
@pos int,             --要获取的数据项的位置
@split varchar(10)     --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
	IF @s IS NULL RETURN(NULL)
	DECLARE @splitlen int
	SELECT @splitlen=LEN(@split+'a')-2
	WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
		SELECT @pos=@pos-1,
			@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
	RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO

select Rut = dbo.f_GetStr([aaa],1,'-') + case when dbo.f_GetStr([aaa],2,'-') = '' then '' else '-' end + dbo.f_GetStr([aaa],2,'-')+ case when dbo.f_GetStr([aaa],3,'-') = '' then '' else '-' end+dbo.f_GetStr([aaa],3,'-') from tA


/*

Rut
-----------
A01-06-B05
A1-7-B3
B3-om
om
A1-7-B3
A1-7-B3

*/

[lxl639]

--测试数据
if not object_id(N'Tempdb..#AAA') is null
    drop table #AAA
Go
Create table #AAA([aaa] nvarchar(1000))
Insert #AAA
select N'A02-065-B035-SdD-EA' union all
select N'A01-06-B05-SD' union all
select N'A1-7-B3-om' union all
select N'A1-7-B3'
Go
--测试数据结束

if not object_id(N'fn_getstrtest') is null
	drop function fn_getstrtest
GO
create function fn_getstrtest(@s nvarchar(1000))
returns nvarchar(1000)
as
begin
	declare @n int=0,@r nvarchar(100)=''
	set @s=@s+'-'
	while @n<4
	begin 
		set @r=@r+left(@s,charindex('-',@s))
		set @s=stuff(@s,1,charindex('-',@s),'')
		set @n=@n+1
	end
	return left(@r,len(@r)-1)
end
GO


SELECT  dbo.fn_getstrtest([aaa]) as newaaa from #AAA

newaaa
----------------------------------------------------------------------------------------------------
A02-065-B035-SdD
A01-06-B05-SD
A1-7-B3-om
A1-7-B3

(4 行受影响)

[lxl639]

--测试数据
if not object_id(N'Tempdb..#AAA') is null
    drop table #AAA
Go
Create table #AAA([aaa] nvarchar(1000))
Insert #AAA
select N'A02-065-B035-SdD-EA' union all
select N'A01-06-B05-SD' union all
select N'A1-7-B3-om' union all
select N'A1-7-B3' union all
select N'A1-8' union all
select N'A1'
Go
--测试数据结束

if not object_id(N'fn_getstrtest') is null
	drop function fn_getstrtest
GO
create function fn_getstrtest(@s nvarchar(1000))
returns nvarchar(1000)
as
begin
	declare @n int=0,@r nvarchar(100)=''
	set @s=@s+'-'
	while @n<4
	begin 
		set @r=@r+left(@s,charindex('-',@s))
		if @s<>''
			set @s=stuff(@s,1,charindex('-',@s),'')
		set @n=@n+1
	end
	return left(@r,len(@r)-1)
end
GO


SELECT  dbo.fn_getstrtest([aaa]) as newaaa from #AAA

newaaa
--------------------------------------------------------------------------------------
A02-065-B035-SdD
A01-06-B05-SD
A1-7-B3-om
A1-7-B3
A1-8
A1

(6 行受影响)

[二月十六]

如果都是只有三个“-”可以试试这个：

--测试数据
if not object_id(N'Tempdb..#AAA') is null
	drop table #AAA
Go
Create table #AAA([aaa] nvarchar(33))
Insert #AAA
select N'A01-06-B05-SD' union all
select N'A1-7-B3-om'
Go
--测试数据结束
SELECT  SUBSTRING(aaa, 1, LEN(aaa) - CHARINDEX('-', REVERSE(aaa)))
FROM    #AAA

[FCWTR]

您好！有的只有2个“-”，有的还有4个的！谢谢

[OwenZeng_DBA]

引用 2 楼 FCWTR 的回复:

您好！有的只有2个“-”，有的还有4个的！谢谢

4个的没问题，只有2个的怎么处理

[FCWTR]

我导出到EXCEL后，用分列删除了最后的，重新导入SQL