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('采购[华中区]','[',']')
/*
华中区
*/