27,579
社区成员
发帖
与我相关
我的任务
分享
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('采购[华中区]','[',']')
/*
华中区
*/
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(@leftIdx),LEN(@str))
SET @rightIdx = CHARINDEX(@right,@r);
IF ISNULL(@rightIdx,0)=0
RETURN '';
RETURN SUBSTRING(@r,0,@rightIdx);
END
GO
SELECT dbo.Fun_GetStrInBracket('采购(华中区)','(',')')
/*
华中区
*/
DECLARE @str NVARCHAR(200)='采购(华中区)'
SELECT SUBSTRING(@str,CHARINDEX('(',@str)+1,CHARINDEX(')',@str)-CHARINDEX('(',@str)-1)
DECLARE @str NVARCHAR(200)='采购(华中区)'
SELECT SUBSTRING(@str,CHARINDEX('(',@str)+1,LEN(@str)-CHARINDEX('(',@str)-1)