Thứ hai, ngày 5 tháng 12 năm 2016

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

Ngày đăng: 7/1/2012, 12:2:54AM | Lượt xem: 6,855
Hot!

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

 

http://hmclip.net/
 Chia sẻ qua: 
Hot!
Ý kiến bạn đọc

These items will be permanently deleted and cannot be recovered. Are you sure?

Gallery

image

Maecenas viverra rutrum pulvinar

Maecenas viverra rutrum pulvinar! Aenean vehicula nulla sit amet metus aliquam et malesuada risus aliquet. Vestibulum rhoncus, dolor sit amet venenatis porta, metus purus sagittis nisl, sodales volutpat elit lorem…

Read more

Text Links

Thiết kế logo chuyên nghiệp Insky
DAFABET
W88 w88b.com/dang-ky-tai-khoan-w88
W88
Copyright © 2011 - 2012 vietshare.vn by phamkhuong102@gmail.com doanhkisi2315@gmail.com. All rights reserved.