• 常用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

    附:后续遇到补充。。。


  • 相关阅读:
    打印沙盒路径
    iOS自定义组与组之间的距离以及视图
    iOS开发之地域选择
    设置按钮文字右对齐
    ios应用下架方法(说明)
    You've implemented -[<UIApplicationDelegate> application:didReceiveRemoteNotification:fetchCompletionHandler:], but you still need to add "remote-notification" to the list of your supported UIBackgrou
    iOS开发-模拟器的小常识
    iOS-本地的推送
    创建节点学习
    窥探一句话木马后门的背后
  • 原文地址:https://www.cnblogs.com/Violety/p/9485330.html
Copyright © 2020-2023  润新知