标量函数：获取括号中的内容

USE tempdb
GO
IF OBJECT_ID('dbo.Fun_GetStrInBracket') IS NOT NULL 
	DROP FUNCTION dbo.Fun_GetStrInBracket
GO
-- =============================================
-- Author:		yenange
-- Create date: 2018-12-16
-- Description:	获取括号中的内容
-- =============================================
CREATE FUNCTION  dbo.Fun_GetStrInBracket
(
	@str NVARCHAR(MAX),
	@left NVARCHAR(50),
	@right NVARCHAR(50)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @r NVARCHAR(MAX),@leftIdx INT,@rightIdx INT
	SET @leftIdx = CHARINDEX(@left,@str);
	SET @rightIdx = CHARINDEX(@right,@str);
	IF ISNULL(@str,'')='' OR ISNULL(CHARINDEX(@left,@str),0)=0
		RETURN '';
		
	SET @r = SUBSTRING(@str,@leftIdx+LEN(@left),LEN(@str))
	SET @rightIdx = CHARINDEX(@right,@r);
	IF ISNULL(@rightIdx,0)=0
		RETURN '';
		
	RETURN SUBSTRING(@r,0,@rightIdx);
END
GO

SELECT dbo.Fun_GetStrInBracket('采购（华中区）','（','）')
SELECT dbo.Fun_GetStrInBracket('采购《《华中区》》','《《','》》')
SELECT dbo.Fun_GetStrInBracket('采购【华中区】','【','】')
SELECT dbo.Fun_GetStrInBracket('采购{华中区}','{','}')
SELECT dbo.Fun_GetStrInBracket('采购[华中区]','[',']')
/*
华中区
 */