 » SQL server » Tổng hợp các hàm SQL server tự định nghĩa có thể hữu ích cho bạn 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.
Tại hmweb ngày trước tôi cũng đã giới thiệu về thủ tục tạo chuỗi ký tự ngẫu
nhiên nhưng là thủ tục thì áp dụng nó hơi khó do đó ở bài viết này tôi viết
thành hàm để dễ xử dụng. Vì ở đâ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
6. Hàm chuyển từ Text Sang DatetimeThông thường khi lập trình trên Form nhập
liệu bạn để cho người dùng nhập kiểu dd/MM/yyyy nhưng trường dữ liệu trong
Database lại là dạng Datetime. Khi đó bạn áp dụng hàm này
CREATE FUNCTION fChuyenTextSangDateTime
(@mNgay nvarchar(10))
RETURNS datetime
AS
BEGIN
DECLARE @mDate datetime
if (len(@mNgay)>=8)--đủ ngày tháng năm
Set @mDate= cast( substring( @mNgay,CHARINDEX('/',@mNgay,0)+1,CHARINDEX('/',@mNgay, CHARINDEX('/',@mNgay,0)+1)-CHARINDEX('/',@mNgay,0)-1 )+'/'+
substring( @mNgay,0,CHARINDEX('/',@mNgay,0))+'/'+
substring(@mNgay, CHARINDEX('/',@mNgay,CHARINDEX('/',@mNgay,0)+1)+1 , len(@mNgay)-CHARINDEX('/',@mNgay,CHARINDEX('/',@mNgay,0)+1))
as datetime)
else
if (len(@mNgay)>4 AND len(@mNgay)<8)--có tháng năm
Set @mDate= cast(substring( @mNgay,0,CHARINDEX('/',@mNgay,0)) +'/01/'+
substring(@mNgay, CHARINDEX('/',@mNgay,0)+1, len(@mNgay)-CHARINDEX('/',@mNgay,0))
as datetime)
else-- có năm
Set @mDate= cast( '01/01/'+ @mNgay as datetime)
return @mDate
END
7. Hàm lấy số ngày của thàng bất kỳ:
Khi bạn cần tính toán số liệu báo cáo như tính trung bình thu nhập của tháng
bất kỳ bạn sẽ cần đến hàm này nếu bạn lập trình trong SQL
CREATE FUNCTION fSoNgayCuaThang (
@mMonth Int,
@mYear Int
)
RETURNS int
AS
BEGIN
DECLARE @mDays Int
IF @mMonth = 2
BEGIN
IF ((@mYear % 4 = 0 AND @mYear % 100 <> 0) OR (@mYear % 400 = 0))
SET @mDays = 29
ELSE
SET @mDays = 28
END
ELSE
SELECT @mDays =
CASE @mMonth
WHEN 1 THEN 31
WHEN 3 THEN 31
WHEN 5 THEN 31
WHEN 7 THEN 31
WHEN 8 THEN 31
WHEN 10 THEN 31
WHEN 12 THEN 31
WHEN 4 THEN 30
WHEN 6 THEN 30
WHEN 9 THEN 30
WHEN 11 THEN 30
END
RETURN @mDays
END
8. Hàm mã hóa ký tự
CREATE FUNCTION fuMaHoaString
(@strInput nvarchar(200),
@dt smallint=-1)
RETURNS nvarchar(200)
AS
BEGIN
Declare @str nvarchar(200)
Declare @lenStr tinyint
Declare @i tinyint
Declare @k smallint
Set @lenStr = Len(@strInput)
Set @str = ''
Set @i = 1
If (@dt = 0)
Set @dt = -1
Set @k = @dt / Abs(@dt)
While (@i <= @lenStr)
Begin
Set @str = @str + Char((Ascii(SubString(@strInput, @i, 1))+@k*20))
Set @k = -@k
Set @i = @i + 1
End
Return @str
END
Ví dụ: SELECT dbo.fuMaHoaString('abc123',1) Sẽ trả về 'uNw F '
9. Hàm giải mã hàm đã mã hóa ở trên
Khi bạn cần tính toán số liệu báo cáo như tính trung bình thu nhập của tháng
bất kỳ bạn sẽ cần đến hàm này nếu bạn lập trình trong SQL
CREATE FUNCTION fuGiaiMaKyTuDaMaHoa
(@strInput nvarchar(200),
@dt smallint=-1)
RETURNS nvarchar(200)
AS
BEGIN
Declare @str nvarchar(200)
Declare @lenStr tinyint
Declare @i tinyint
Declare @k smallint
Set @lenStr = Len(@strInput)
Set @str = ''
Set @i = 1
If (@dt = 0)
Set @dt = -1
Set @k = -@dt / Abs(@dt)
While (@i <= @lenStr)
Begin
Set @str = @str + Char((Ascii(SubString(@strInput, @i, 1))+@k*20))
Set @k = -@k
Set @i = @i + 1
End
Return @str
ENDVí dụ: SELECT dbo.fuGiaiMaKyTuDaMaHoa(dbo.fuMaHoaString('abc123',1),1) Sẽ trả về abc123
Còn tiếp ... |