hmclip
20-12-2011, 22:50
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
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 ,*** ,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 ,*** ,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 ,*** ,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(@str Input, @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 ,*** ,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 ,*** ,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 ,*** ,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(@s trInput, @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='abcdefghijklmnopqrstuvwxyzABCDEF GHIJKLMNOPQRSTUVWXYZ1234567890'
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
Còn tiếp ... Bạn có thể xem các hàm khác tại http://hmclip.net/Chia-Se-La-Niem-Vui/sql-server/tong-hop-cac-ham-sql-server-tu-dinh-nghia-co-the-huu-ich-cho-ban.htm
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 ,*** ,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 ,*** ,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 ,*** ,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(@str Input, @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 ,*** ,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 ,*** ,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 ,*** ,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(@s trInput, @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='abcdefghijklmnopqrstuvwxyzABCDEF GHIJKLMNOPQRSTUVWXYZ1234567890'
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
Còn tiếp ... Bạn có thể xem các hàm khác tại http://hmclip.net/Chia-Se-La-Niem-Vui/sql-server/tong-hop-cac-ham-sql-server-tu-dinh-nghia-co-the-huu-ich-cho-ban.htm