declare @str varchar(100) set @str='AAAAA,BBBBBBBBB,CCCCC,DDDD,EEEE'select charindex(',',@str,charindex(',',@str,charindex(',',@str)+1)+1)
-- ================================================ -- Template generated from Template Explorer using: -- Create Scalar Function (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the function. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Name -- Create date: -- Description: -- ============================================= CREATE FUNCTION GetPatternIndex ( -- Add the parameters for the function here @Pattern nvarchar(100), @Expression nvarchar(100), @PatternIndex int ) RETURNS int AS BEGIN -- Declare the return variable here DECLARE @Result int -- Add the T-SQL statements to compute the return value here declare @i int declare @j int set @i = @PatternIndex set @j = 0
while @i > 0 begin select @j = Charindex(@Pattern, @Expression, @j + 1) set @i = @i - 1 end set @Result = @j -- Return the result of the function RETURN @ResultEND GO
set @str='AAAAA,BBBBBBBBB,CCCCC,DDDD,EEEE'select charindex(',',@str,charindex(',',@str,charindex(',',@str)+1)+1)
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION GetPatternIndex
(
-- Add the parameters for the function here
@Pattern nvarchar(100),
@Expression nvarchar(100),
@PatternIndex int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int -- Add the T-SQL statements to compute the return value here
declare @i int
declare @j int
set @i = @PatternIndex
set @j = 0
while @i > 0
begin
select @j = Charindex(@Pattern, @Expression, @j + 1)
set @i = @i - 1
end set @Result = @j
-- Return the result of the function
RETURN @ResultEND
GO
--返回第N个字符前的字符(不含那个字符)
select left('AAAAA,BBBBBBBBB,CCCCC,DDDD,EEEE',dbo.GetPatternIndex(',','AAAAA,BBBBBBBBB,CCCCC,DDDD,EEEE', 3)-1)--返回第N个字符后的字符(不含那个字符)
select Right('AAAAA,BBBBBBBBB,CCCCC,DDDD,EEEE',len('AAAAA,BBBBBBBBB,CCCCC,DDDD,EEEE')-dbo.GetPatternIndex(',', 'AAAAA,BBBBBBBBB,CCCCC,DDDD,EEEE', 3))