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