常用Sql 标量值函数
1、汉字拼音首字母
/****** Object: UserDefinedFunction [dbo].[fnGetHzPY] Script Date: 08/16/2018 09:04:47 Description:获取汉字拼音首字母(简拼) ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fnGetHzPY] ( @str NVARCHAR(4000) ) RETURNS NVARCHAR(4000) AS BEGIN DECLARE @WORD nchar(1), @PY nvarchar(4000) SET @PY='' WHILE LEN(@str) > 0 BEGIN SET @WORD = left(@str,1) --如果非汉字字符,返回原字符 SET @PY = @PY + (CASE WHEN UNICODE(@WORD) BETWEEN 19968 and 19968 + 20901 THEN (SELECT top 1 PY FROM ( SELECT 'A' as PY,N'驁' as WORD UNION all SELECT 'B',N'簿' UNION all SELECT 'C',N'錯' UNION all SELECT 'D',N'鵽' UNION all SELECT 'E',N'樲' UNION all SELECT 'F',N'鰒' UNION all SELECT 'G',N'腂' UNION all SELECT 'H',N'夻' UNION all SELECT 'J',N'攈' UNION all SELECT 'K',N'穒' UNION all SELECT 'L',N'鱳' UNION all SELECT 'M',N'旀' UNION all SELECT 'N',N'桛' UNION all SELECT 'O',N'漚' UNION all SELECT 'P',N'曝' UNION all SELECT 'Q',N'囕' UNION all SELECT 'R',N'鶸' UNION all SELECT 'S',N'蜶' UNION all SELECT 'T',N'籜' UNION all SELECT 'W',N'鶩' UNION all SELECT 'X',N'鑂' UNION all SELECT 'Y',N'韻' UNION all SELECT 'Z',N'咗' ) T WHERE WORD >= @WORD COLLATE Chinese_PRC_CS_AS_KS_WS ORDER BY PY ASC) ELSE @WORD END) SET @str = Right(@str,LEN(@str) - 1) END RETURN @PY END GO
2、将数字转换为中文数字
/****** Object: UserDefinedFunction [dbo].[fnGetChinaStringA] Script Date: 08/16/2018 09:11:59 Description:将数字转换成中文数字 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fnGetChinaNumber] ( @Word INT ) RETURNS VARCHAR(256) AS BEGIN DECLARE @varChinaDateString VARCHAR(256), @varReturn VARCHAR(256), @varWord VARCHAR(256), @I SMALLINT SET @varChinaDateString = '〇一二三四五六七八九' SET @varWord = CAST(@Word AS VARCHAR) SET @I = 1 SET @varReturn = '' WHILE @I <= LEN(@varWord) BEGIN SET @varReturn = @varReturn + SUBSTRING(@varChinaDateString, SUBSTRING(@varWord,@I,1) + 1, 1) SET @I = @I + 1 END RETURN @varReturn END GO
3、函数:将字符串转换为Table数据集
/****** Object: UserDefinedFunction [dbo].[fnParaSplit] Script Date: 08/16/2018 09:25:38 Description:将字符串转换成表 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fnParaSplit] ( @SplitString NVARCHAR(MAX), @Separator CHAR(1) = ',' ) RETURNS @SplitStringsTable TABLE ( [FID] INT IDENTITY(1, 1), [Item] VARCHAR(1000) ) AS BEGIN DECLARE @CurrentIndex INT, @NextIndex INT, @ReturnText VARCHAR(1000) SET @CurrentIndex = 1 WHILE (@CurrentIndex <= DATALENGTH(@SplitString) / 2) BEGIN SET @NextIndex = CHARINDEX(@Separator, @SplitString, @CurrentIndex) IF(@NextIndex = 0 OR @NextIndex IS NULL) SELECT @NextIndex = DATALENGTH(@SplitString) / 2 + 1 SET @ReturnText = CONVERT(VARCHAR(1000), SUBSTRING(@SplitString, @CurrentIndex, @NextIndex-@CurrentIndex)) INSERT INTO @SplitStringsTable ([Item]) VALUES (@ReturnText) SET @CurrentIndex = @NextIndex + 1 END RETURN END GO
附:后续遇到补充。。。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步