字符串截取问题

Question

select SUBSTRING('abscd  efsx',1,CHARINDEX(' ','abscd  efsx')) 
/*
abscd 
*/

Answer 1

select left('abscd efsx',charindex(' ','abscd  efsx')-1)

Answer 2

--写一下自定义的split函数
--调用：SELECT * FROM [DB_OA].[dbo].[ufn_Split] ('abscd  efsx','  ')CREATE FUNCTION [dbo].[ufn_Split]
(
@SplitString varchar(8000),-- nvarchar(4000)
@Separator varchar(2) = ','
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] varchar(8000)-- nvarchar(4000)
)
AS
BEGIN
    DECLARE @CurrentIndex int;
    DECLARE @NextIndex int;
    DECLARE @ReturnText varchar(8000);-- nvarchar(4000)
    SELECT @CurrentIndex=1;
    WHILE(@CurrentIndex<=len(@SplitString))
    BEGIN
        SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
        IF(@NextIndex=0 OR @NextIndex IS NULL)
            SELECT @NextIndex=len(@SplitString)+1;
       
        SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);        INSERT INTO @SplitStringsTable([value])
        VALUES(@ReturnText);
       
        SELECT @CurrentIndex=@NextIndex+1;
    END
    RETURN;
END