Tổng hợp các hàm SQL server tự định nghĩa có thể hữu ích cho bạn

Trong quá trình lập trình nếu bạn làm việc nhiều với cơ sở dữ liệu SQL server chắc hẳn bạn sẽ gặp nhiều vấn đề cần thực hiện và tìm cách xây dựng các hàm cho riêng mình. Các hàm này sẽ rất hữu ích với vấn đề mà người khác đang cần. Vì lý do đó tôi chia sẻ lên các hàm mà tôi đã viết hoặc đã sưu tầm mà tôi đã và đang sử dụng. Có rất nhiều hàm và thủ tục mà tại thời điểm này tôi không nhớ hết do đó tôi sẽ cập nhật dần để các bạn tham khảo

1. Hàm convert chuỗi TCVN sang Unicode

CREATE Function [dbo].[fTCVNToUnicode](
     @strInput VARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @strOutput NVARCHAR(4000)
    DECLARE @TCVN CHAR(671)
    DECLARE @UNICODE CHAR(671)
    SET @TCVN = ',184 ,181 ,182 ,183 ,185 ,168 ,190 ,187 ,188 ,189 ,198 ,169 ,202 ,199 ,200 ,201 ,203 ,208 ,204 ,206 ,207 ,209 ,170 ,213 ,210 ,211 ,212 ,214 ,221 ,215 ,216 ,220 ,222 ,227 ,223 ,225 ,226 ,228 ,171 ,232 ,229 ,230 ,231 ,233 ,172 ,237 ,234 ,235 ,236 ,238 ,243 ,239 ,241 ,242 ,244 ,173 ,248 ,245 ,246 ,247 ,249 ,253 ,250 ,251 ,252 ,254 ,174 ,184 ,181 ,182 ,183 ,185 ,161 ,190 ,187 ,188 ,189 ,198 ,162 ,202 ,199 ,200 ,201 ,203 ,208 ,204 ,206 ,207 ,209 ,163 ,213 ,210 ,211 ,212 ,214 ,221 ,215 ,216 ,220 ,222 ,227 ,223 ,225 ,226 ,228 ,164 ,232 ,229 ,230 ,231 ,233 ,165 ,237 ,234 ,235 ,236 ,238 ,243 ,239 ,241 ,242 ,244 ,166 ,248 ,245 ,246 ,247 ,249 ,253 ,250 ,251 ,252 ,254 ,167 ,'
    SET @UNICODE = ',225 ,224 ,7843,227 ,7841,259 ,7855,7857,7859,7861,7863,226 ,7845,7847,7849,7851,7853,233 ,232 ,7867,7869,7865,234 ,7871,7873,7875,7877,7879,237 ,236 ,7881,297 ,7883,243 ,242 ,7887,245 ,7885,244 ,7889,7891,7893,7895,7897,417 ,7899,7901,7903,7905,7907,250 ,249 ,7911,361 ,7909,432 ,7913,7915,7917,7919,7921,253 ,7923,7927,7929,7925,273 ,193 ,192 ,7842,195 ,7840,258 ,7854,7856,7858,7860,7862,194 ,7844,7846,7848,7850,7852,201 ,200 ,7866,7868,7864,202 ,7870,7872,7874,7876,7878,205 ,204 ,7880,296 ,7882,211 ,210 ,7886,213 ,7884,212 ,7888,7890,7892,7894,7896,416 ,7898,7900,7902,7904,7906,218 ,217 ,7910,360 ,7908,431 ,7912,7914,7916,7918,7920,221 ,7922,7926,7928,7924,272 ,'
    DECLARE @COUNTER INT
    DECLARE @POSITION INT
    SET @COUNTER = 1
    SET @strOutput = ''
    WHILE (@COUNTER <= LEN(@strInput))
    BEGIN
        SET @POSITION = CHARINDEX(','+CONVERT(CHAR(4),ASCII(SUBSTRING(@strInput, @COUNTER, 1)))+',', @TCVN, 1)
        IF @POSITION > 0
        begin     
              SET @strOutput = @strOutput + NCHAR(CONVERT(INT,SUBSTRING(@UNICODE, @POSITION+1, 4)))
        end
        ELSE
              SET @strOutput = @strOutput + SUBSTRING(@strInput, @COUNTER, 1)
    SET @COUNTER = @COUNTER + 1
    END
    return @strOutput
END

2. Hàm convert chuỗi Unicode sang TCVN

 CREATE FUNCTION Unicode2TCVN (@strInput NVARCHAR(4000))
RETURNS VARCHAR(4000)
AS
Begin
    DECLARE @strOutput NVARCHAR(4000), @COUNTER INT, @POSITION INT
    SET @COUNTER = 1 SET @strOutput = ''
    DECLARE @TCVN CHAR(671), @UNICODE CHAR(671)
    SET @TCVN = ',184 ,181 ,182 ,183 ,185 ,168 ,190 ,187 ,188 ,189 ,198 ,169 ,202 ,199 ,200 ,201 ,203 ,208 ,204 ,206 ,207 ,209 ,170 ,213 ,210 ,211 ,212 ,214 ,221 ,215 ,216 ,220 ,222 ,227 ,223 ,225 ,226 ,228 ,171 ,232 ,229 ,230 ,231 ,233 ,172 ,237 ,234 ,235 ,236 ,238 ,243 ,239 ,241 ,242 ,244 ,173 ,248 ,245 ,246 ,247 ,249 ,253 ,250 ,251 ,252 ,254 ,174 ,184 ,181 ,182 ,183 ,185 ,161 ,190 ,187 ,188 ,189 ,198 ,162 ,202 ,199 ,200 ,201 ,203 ,208 ,204 ,206 ,207 ,209 ,163 ,213 ,210 ,211 ,212 ,214 ,221 ,215 ,216 ,220 ,222 ,227 ,223 ,225 ,226 ,228 ,164 ,232 ,229 ,230 ,231 ,233 ,165 ,237 ,234 ,235 ,236 ,238 ,243 ,239 ,241 ,242 ,244 ,166 ,248 ,245 ,246 ,247 ,249 ,253 ,250 ,251 ,252 ,254 ,167 ,'
    SET @UNICODE = ',225 ,224 ,7843,227 ,7841,259 ,7855,7857,7859,7861,7863,226 ,7845,7847,7849,7851,7853,233 ,232 ,7867,7869,7865,234 ,7871,7873,7875,7877,7879,237 ,236 ,7881,297 ,7883,243 ,242 ,7887,245 ,7885,244 ,7889,7891,7893,7895,7897,417 ,7899,7901,7903,7905,7907,250 ,249 ,7911,361 ,7909,432 ,7913,7915,7917,7919,7921,253 ,7923,7927,7929,7925,273 ,193 ,192 ,7842,195 ,7840,258 ,7854,7856,7858,7860,7862,194 ,7844,7846,7848,7850,7852,201 ,200 ,7866,7868,7864,202 ,7870,7872,7874,7876,7878,205 ,204 ,7880,296 ,7882,211 ,210 ,7886,213 ,7884,212 ,7888,7890,7892,7894,7896,416 ,7898,7900,7902,7904,7906,218 ,217 ,7910,360 ,7908,431 ,7912,7914,7916,7918,7920,221 ,7922,7926,7928,7924,272 ,'
     WHILE (@COUNTER <= LEN(@strInput))
        BEGIN
            SET @POSITION = CHARINDEX(','+CONVERT(CHAR(4),UNICODE(SUBSTRING(@strInput, @COUNTER, 1)))+',', @UNICODE, 1)
            IF @POSITION > 0
                Begin
                    SET @strOutput = @strOutput + CHAR(CONVERT(INT,SUBSTRING(@TCVN, @POSITION+1, 4)))
                End
            Else
            SET @strOutput = @strOutput + SUBSTRING(@strInput, @COUNTER, 1)
            SET @COUNTER = @COUNTER + 1
        END
    RETURN @strOutput
END

Hai hàm này sẽ là quá tuyệt với với vấn đề là khi dữ liệu bạn nhận được là TCNV giờ muốn convert sang Unicode hoặc ngược lại

3. Hàm tạo chuỗi ký tự ngẫu nhiên.

Ở đây ta có dùng hàm Rand() trong hàm mà khi viết hàm không được sử dụng hàm này do đó trước tiên  ta cần tạo một View để lấy ra số Rand này:

CREATE VIEW vw_Random
AS
SELECT rand() as Random

Sử dụng view này trong hàm như sau

CREATE FUNCTION GeneratePassword(@Length int)
RETURNS varchar(32)
AS
BEGIN
    DECLARE @RandomID varchar(32)
    DECLARE @counter smallint
    DECLARE @RandomNumber float
    DECLARE @RandomNumberInt tinyint
    DECLARE @CurrentCharacter varchar(1)
    DECLARE @ValidCharacters varchar(255)
    SET @ValidCharacters='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'
    DECLARE @ValidCharactersLength int
    SET @ValidCharactersLength = len(@ValidCharacters)
    SET @CurrentCharacter = ''
    SET @RandomNumber = 0
    SET @RandomNumberInt = 0
    SET @RandomID = ''
    SET @counter = 1
    WHILE @counter < (@Length + 1)
    BEGIN
        SET @RandomNumber = (SELECT random from vw_random)
        SET @RandomNumberInt = Convert(tinyint,
            ((@ValidCharactersLength - 1) * @RandomNumber + 1))
        SELECT @CurrentCharacter =
            SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)
        SET @counter = @counter + 1
        SET @RandomID = @RandomID + @CurrentCharacter
    END
    RETURN @RandomID
END

Giờ bạn muốn chuỗi ngẫu nhiên của bạn có bao nhiêu ký tự bạn chỉ việc truyền tham số vào. Ví dụ SELECT dbo.GeneratePassword(13) => OyTU2FhiKBTgn Với mỗi lần Chạy lệnh này sẽ cho bạn chuỗi 13 ký tự khác nhau. Ứng dụng hàm này bạn có thể tạo chuỗi mật khẩu ngẫu nhiên dùng cho chức năng quên pass của thành viên

4. Hàm trả về danh sách tất cả các ngày trong tuần

Khi cần tạo báo cáo theo tuần bạn muốn list theo danh sách các ngày của một tuần nào đó bạn có thể tham khảo hàm sau:

CREATE FUNCTION getAllDayOfWeek
(
    @Date dateTime
)
RETURNS @_Nam TABLE (mdate DateTime)
BEGIN

    Declare @intdate int SET @intdate = 0
    SELECT @intdate = (DATEPART(dw, @Date-2) + @@DATEFIRST) % 7
    INSERT INTO @_Nam
        SELECT @Date- @intdate
    SET @intdate = @intdate - 1
    INSERT INTO @_Nam
    SELECT @Date - @intdate
    SET @intdate = @intdate - 1
    INSERT INTO @_Nam
    SELECT @Date - @intdate
    SET @intdate = @intdate - 1
    INSERT INTO @_Nam
    SELECT @Date - @intdate
    SET @intdate = @intdate - 1
    INSERT INTO @_Nam
    SELECT @Date - @intdate
    SET @intdate = @intdate - 1
    INSERT INTO @_Nam
    SELECT @Date- @intdate
    SET @intdate = @intdate - 1
    INSERT INTO @_Nam
    SELECT @Date- @intdate
    SET @intdate = @intdate - 1
    RETURN   
END

Ví du: SELECT * FROM dbo.getDayOfWeek(getdate()) Sẽ cho ta tất cả các ngày của tuần hiện tại

5. Hàm Split trong SQL

Trong SQL không có hàm Split nhưng ta cũn có thể xây dựng hàm này để áp dụng, Kết quả trả về của hàm này là một bảng

 CREATE FUNCTION [dbo].[fnSplit](
    @strInput NVARCHAR(4000),
    @char char(1))
RETURNS @Tbl TABLE (id int IDENTITY(1,1),part NVARCHAR(1000))
AS
BEGIN
    DECLARE @SubStr NVARCHAR(100), @i INT

    SET @i = CHARINDEX(@char, @strInput, 0)
    WHILE @i > 0
    BEGIN
        SET @SubStr = LEFT(@strInput,@i-1)
        INSERT INTO @Tbl
        SELECT @SubStr
        SET @strInput = SUBSTRING(@strInput, @i+1,4000)
        SET @i = CHARINDEX(@char, @strInput, 0)
    END  
    INSERT INTO @Tbl
    SELECT LTRIM(RTRIM(@strInput))
    RETURN
END

Theo HMWEB(còn nữa)