由于数据中的字符串比较长,显示出来效果不是很好,所以想只显示字段中第二个空格前的数据,编写SQL函数
GO
/****** Object: UserDefinedFunction [dbo].[GroupRows] Script Date: 12/18/2017 1:56:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[GetTwoWord](@SuplierName varchar(100))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @result varchar(8000)
DECLARE @temp varchar(100)
DECLARE @SpaceCount int--varchar(100)
SET @result = ltrim(@SuplierName)
SET @temp = ltrim(@SuplierName)
SET @SpaceCount = 0
while( CHARINDEX(' ',@temp) > 0 )
begin
SET @temp = SUBSTRING(@temp,CHARINDEX(' ',@temp) +1,LEN(@temp))
SET @SpaceCount = @SpaceCount + 1
if ( @SpaceCount >= 2 )
begin
SET @result = SUBSTRING(@SuplierName,1,LEN(@SuplierName)-LEN(@temp)) -- @temp
return @result
end
end
RETURN @result
END