1.Use jQuery to Find Value of a .NET Textbox
2.NET Gridview: Fire jQuery Alert When Table Row is Clicked
3.Select Data Base all primary key
SELECT p.TABLE_NAME,c.CONSTRAINT_NAME,c.COLUMN_NAME ,P.CONSTRAINT_SCHEMA
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = p.TABLE_NAME
AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME
ORDER by c.TABLE_NAME
4.SQL SERVER FUNCTION
Day = DATENAME(DW, Date),
DayOfWeek = DATEPART(WEEKDAY, Date),
DayOfMonth = DAY(Date),
DayOfYear = DATEPART(DY,Date),
PreviousDay = DATEADD(DAY, -1, Date),
NextDay = DATEADD(DAY, 1, Date),
WeekOfYear = DATEPART(WK,Date),
Month = DATENAME(MONTH,Date),
MonthOfYear = MONTH(Date),
QuarterOfYear = DATEPART(Q, Date),
Year = YEAR(Date);
declare @DefaultDateFirst int
set @DefaultDateFirst = @@datefirst
--; 7 First day of week is "Sunday" by default
select [@DefaultDateFirst] = @DefaultDateFirst
set datefirst @DefaultDateFirst
select datename(dw,getdate()) -- Saturday
select datepart(dw,getdate()) -- 7
--; Set the first day of week to * TUESDAY *
--; (some people start their week on Tuesdays...)
set datefirst 2
select datename(dw,getdate()) -- Saturday
--; Returns 5 because Saturday is the 5th day since Tuesday.
--; Tue 1, Wed 2, Th 3, Fri 4, Sat 5
select datepart(dw,getdate()) -- 5 <-- It's not 7!
set datefirst @DefaultDateFirst
select DATEPART(WEEKDAY, GETDATE()) as WeekDayNo,
DATENAME(DW, GETDATE()) as WeekDayName
========================================================================
5. Sql Server function
CREATE FUNCTION AddWorkDays
(
@WorkingDays As Int,
@StartDate AS DateTime
)
RETURNS DateTime
AS
BEGIN
DECLARE @Count AS Int
DECLARE @i As Int
DECLARE @NewDate As DateTime
SET @Count = 0
SET @i = 0
WHILE (@i < @WorkingDays) --runs through the number of days to add
BEGIN
-- increments the count variable
SELECT @Count = @Count + 1
-- increments the i variable
SELECT @i = @i + 1
-- adds the count on to the StartDate and checks if this new date is a Saturday or Sunday
-- if it is a Saturday or Sunday it enters the nested while loop and increments the count variable
WHILE DATEPART(weekday,DATEADD(d, @Count, @StartDate)) IN (1,7)
BEGIN
SELECT @Count = @Count + 1
END
END
-- adds the eventual count on to the Start Date and returns the new date
SELECT @NewDate = DATEADD(d,@Count,@StartDate)
RETURN @NewDate
END
GO
=======================
ALTER FUNCTION [dbo].[AddWorkDays] (@WorkingDays As Int,@StartDate AS DateTime) RETURNS DateTime
AS BEGIN
DECLARE @Count AS Int
DECLARE @i As Int
DECLARE @NewDate As DateTime
DECLARE @CurrentFirstDayInWeek int
DECLARE @DateFirstDifference int
DECLARE @SatNo int
DECLARE @SunNo int
SELECT @CurrentFirstDayInWeek = @@DATEFIRST
SET @DateFirstDifference = 7 - @CurrentFirstDayInWeek
SET @SatNo = 7
SET @SunNo = 1
IF @DateFirstDifference > 0
BEGIN
SET @SatNo = @DateFirstDifference
SET @SunNo = 1 + @DateFirstDifference
END
SET @Count = 0
SET @i = 0
WHILE (@i < @WorkingDays) --runs through the number of days to add
BEGIN
-- increments the count variable
SELECT @Count = @Count + 1
-- increments the i variable
SELECT @i = @i + 1
-- adds the count on to the StartDate and checks if this new date is a Saturday or Sunday
-- if it is a Saturday or Sunday it enters the nested while loop and increments the count variable
WHILE DATEPART(weekday,DATEADD(d, @Count, @StartDate)) IN (@SatNo, @SunNo)
BEGIN
SELECT @Count = @Count + 1
END
END
-- adds the eventual count on to the Start Date and returns the new date
SELECT @NewDate = DATEADD(d,@Count,@StartDate)
RETURN @NewDate
END
========================
1. Email Validation:
This function below basically checks for Nulls, invalid or duplicate characters, and looks for '@' and '.' in the formatting. Once created, you will run the following t-sql:
select dbo.udf_ValidateEmail('myemailaddress@somedomain.com')
Create FUNCTION udf_ValidateEmail (@email varChar(255))
RETURNS bit
AS
begin
return
(
select
Case
When @Email is null then 0 --NULL Email is invalid
When charindex(' ', @email) <> 0 or --Check for invalid character
charindex('/', @email) <> 0 or --Check for invalid character
charindex(':', @email) <> 0 or --Check for invalid character
charindex(';', @email) <> 0 then 0 --Check for invalid character
When len(@Email)-1 <= charindex('.', @Email) then 0--check for '%._' at end of string
When @Email like '%@%@%'or
@Email Not Like '%@%.%' then 0--Check for duplicate @ or invalid format
Else 1
END
)
end
2. Social Security Number - SSN Validation:
We all know that our SSNs are 9-digits long, and follows the standard format of xxx-xx-xxxx. Therefore, we would want to validate that it is in fact 9-digits, the characters are numeric [0-9], and ensure that the format is indeed the right length for each section - ie: 3-2-4. After you create this nice function, you can test it by running various offshoots (try to enter invalid data), but basically looks like:
select dbo.udf_ValidateSSN('123-45-6789')
CREATE FUNCTION udf_ValidateSSN(@SSN varchar(50))
RETURNS BIT
AS
BEGIN
DECLARE @ValidFlag BIT
SET @ValidFlag = 0
IF @SSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
BEGIN
IF LEFT(@SSN,3) != '000' AND SUBSTRING(@SSN,5,2) != '00'
AND RIGHT(@SSN,4) != '0000'
BEGIN
SET @ValidFlag = 1
RETURN ( @ValidFlag)
END
END
IF @SSN LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
BEGIN
IF LEFT(@SSN,3) != '000' AND SUBSTRING(@SSN,4,2) != '00'
AND RIGHT(@SSN,4) != '0000'
BEGIN
SET @ValidFlag = 1
RETURN ( @ValidFlag)
END
END
RETURN ( @ValidFlag)
END
GO
3. Validate/Format Phone Number:
There's always confusion on how to enter a phone number, unless there's a note next to the field that says: "xxx-xxx-xxxx" or "(xxx) xxx-xxxx" or even xxxxxxxxxx. A smart function will take all inputs and simply format it the way the end-user wants it stored in the database. For this we can validate the input, and standardize the format of the entered telephone number. This cool function is a bit more sophisticated, in that it does two things, validate the data; and format it to a standard form. It first checks if the data is numeric, is 10-digits in length, does not have a '1' at the beginning, etc. What it wants the user to enter is, 10-digits, no dashes, and it will format '3124457689' to '312-445-7689'. I'm sure you can modify it to accept the dashes. You can test it out as:
select [dbo].[udf_ValFormatPhone] ('3124445678')
CREATE FUNCTION [dbo].[udf_ValFormatPhone] (@phone NVARCHAR(255))
RETURNS NVARCHAR(255)
AS
BEGIN--function
declare @i int, @repCount int
declare @current_char char(1)
declare @phone_new varchar(50)
set @phone_new = rtrim(ltrim(@phone))
if left(@phone_new, 1) = '1'
set @phone_new = right(@phone_new, len(@phone_new) -1)
set @i = 1
while @i <= len(@phone)
begin
set @repCount = 0
if @i > len(@phone_new)
break
set @current_char = substring(@phone_new, @i, 1)
if isnumeric(@current_char) <> 1
begin
set @repCount = len(@phone_new) - len(replace(@phone_new, @current_char, ''))
set @phone_new = replace(@phone_new, @current_char, '')
end
set @i = @i + 1 - @repCount
end
if isnumeric(@phone_new) = 1 and len(@phone_new) = 10
set @phone_new =
substring(@phone_new, 1,3) + '-' +
substring(@phone_new, 4,3) + '-' +
substring(@phone_new, 7,4)
else
set @phone_new = 'invalid entry'
return @phone_new
END --function
4. Validate URL -
This one is an interesting and neat function, a bit unique, and one that I can source back to and credit to Adam Machanic. (sp_configure 'Show Advanced Options',1
go
reconfigure
go
sp_configure 'Ole Automation Procedures',1
go
reconfigure
go
And, then, once you create the function, you can run:
SELECT dbo.udf_ValidateURL('http://www.microsoft.com/sql')
---
1 <--if valid will return 1, otherwise 0
CREATE FUNCTION dbo.ValidateURL(@URL VARCHAR(300))
RETURNS BIT
AS BEGIN
DECLARE @Object INT
DECLARE @Return TINYINT
DECLARE @Valid BIT SET @Valid = 0 --default to false
--create the XMLHTTP object
EXEC @Return = sp_oacreate 'MSXML2.ServerXMLHTTP.3.0', @Object OUTPUT
IF @Return = 0
BEGIN
DECLARE @Method VARCHAR(350) --define setTimeouts method --Resolve, Connect, Send, Receive
SET @Method = 'setTimeouts(45000, 45000, 45000, 45000)' --set the timeouts
EXEC @Return = sp_oamethod @Object, @Method
IF @Return = 0
BEGIN --define open method
SET @Method = 'open("GET", "' + @URL + '", false)'--Open the connection
EXEC @Return = sp_oamethod @Object, @Method
END IF @Return = 0
BEGIN --SEND the request
EXEC @Return = sp_oamethod @Object, 'send()'
=====================================================================
private void ShowMessage(int count)
{
StringBuilder sb = new StringBuilder();
sb.Append("");
ClientScript.RegisterStartupScript(this.GetType(),
"script", sb.ToString());
}
=======================================================================
Here is a MIME64 encoder function written entirely in T-SQL
As long as the copyright notice is visible within the function declaration
and you include a note in the documentation of your system that these
functions are written by me, you may use these functions for free of charge.
If you intend to use these functions in a commercial application,
you are required to email me about your system and provide an email address
to the application manager.
If you like the code and use it just for fun, send an e-mail to me
and tell me about your experience with these functions.
I hope you enjoy these functions. I spent about two hours
writing them because I could not find them on the Internet.
CREATE FUNCTION dbo.fnMIME64Encode
(
@RFC3548 BIT = 1,
@PlainText VARCHAR(6000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Characters VARCHAR(64), @Index SMALLINT,
@m1 TINYINT, @m2 TINYINT, @m3 TINYINT, @m4 TINYINT,
@MimeText VARCHAR(8000), @FinalBlock TINYINT
SELECT @Characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
@FinalBlock = DATALENGTH(@PlainText) % 3,
@PlainText = @PlainText + REPLICATE(CHAR(0), (3 - DATALENGTH(@PlainText) % 3) % 3),
@Index = DATALENGTH(@PlainText) - 2,
@MimeText = ''
WHILE @Index > 0
BEGIN
SELECT @m1 = (ASCII(SUBSTRING(@PlainText, @Index, 1)) & 252) / 4,
@m2 = (ASCII(SUBSTRING(@PlainText, @Index, 1)) & 3) * 16 + (ASCII(SUBSTRING(@PlainText, @Index + 1, 1)) & 240) / 16,
@m3 = (ASCII(SUBSTRING(@PlainText, @Index + 1, 1)) & 15) * 4 + (ASCII(SUBSTRING(@PlainText, @Index + 2, 1)) & 192) / 64,
@m4 = ASCII(SUBSTRING(@PlainText, @Index + 2, 1)) & 63
SELECT @MimeText = CASE
WHEN @FinalBlock = 1 THEN SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + '=='
WHEN @FinalBlock = 2 THEN SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + SUBSTRING(@Characters, @m3 + 1, 1) + '='
ELSE SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + SUBSTRING(@Characters, @m3 + 1, 1) + SUBSTRING(@Characters, @m4 + 1, 1)
END + @MimeText,
@Index = @Index - 3,
@FinalBlock = 0
END
IF @RFC3548 = 1
BEGIN
SELECT @Index = 1 + DATALENGTH(@MimeText) - DATALENGTH(@MimeText) % 76
IF @Index > DATALENGTH(@MimeText)
SELECT @Index = @Index - 76
WHILE @Index > 1
SELECT @MimeText = STUFF(@MimeText, @Index, 0, CHAR(13) + CHAR(10)),
@Index = @Index - 76
END
RETURN @MimeText
END
and of course the MIME64 decoder function
CREATE FUNCTION dbo.fnMIME64Decode
(
@MimeText VARCHAR(8000)
)
RETURNS VARCHAR(6000)
AS
BEGIN
-- © 2006 Peter Larsson, Developer Workshop, all rights reserved
DECLARE @Characters VARCHAR(64), @Index SMALLINT,
@m1 TINYINT, @m2 TINYINT, @m3 SMALLINT, @m4 SMALLINT,
@p1 TINYINT, @p2 TINYINT, @p3 TINYINT,
@PlainText VARCHAR(6000), @Paddings TINYINT
SELECT @Characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
@MimeText = REPLACE(REPLACE(@MimeText, CHAR(13), ''), CHAR(10), ''),
@Index = DATALENGTH(@MimeText) - 3,
@Paddings = DATALENGTH(@MimeText) - DATALENGTH(REPLACE(@MimeText, '=', '')),
@PlainText = ''
WHILE @Index > 0
SELECT @m1 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
@m2 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 1, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
@m3 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 2, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
@m4 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 3, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
@p1 = (@m1 & 63) * 4 + (@m2 & 48) / 16,
@p2 = (@m2 & 15) * 16 + (@m3 & 60) / 4,
@p3 = (@m3 & 3) * 64 + (@m4 & 63),
@PlainText = CHAR(@p1) + CHAR(@p2) + CHAR(@p3) + @PlainText,
@Index = @Index - 4
RETURN LEFT(@PlainText, DATALENGTH(@PlainText) - @Paddings)
END
==================================================================
Password Incription
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[fnInitRc4] Script Date: 09/21/2012 20:40:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnInitRc4]
(
@Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS
BEGIN
DECLARE @Key TABLE (i TINYINT, v TINYINT)
DECLARE @Index SMALLINT,
@PwdLen TINYINT
SELECT @Index = 0,
@PwdLen = LEN(@Pwd)
WHILE @Index <= 255
BEGIN
INSERT @Key
(
i,
v
)
VALUES (
@Index,
ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
)
INSERT @Box
(
i,
v
)
VALUES (
@Index,
@Index
)
SELECT @Index = @Index + 1
END
DECLARE @t TINYINT,
@b SMALLINT
SELECT @Index = 0,
@b = 0
WHILE @Index <= 255
BEGIN
SELECT @b = (@b + b.v + k.v) % 256
FROM @Box AS b
INNER JOIN @Key AS k ON k.i = b.i
WHERE b.i = @Index
SELECT @t = v
FROM @Box
WHERE i = @Index
UPDATE b1
SET b1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
FROM @Box b1
WHERE b1.i = @Index
UPDATE @Box
SET v = @t
WHERE i = @b
SELECT @Index = @Index + 1
END
RETURN
END
ALTER FUNCTION [dbo].[fnEncDecRc4]
(
@Pwd VARCHAR(256),
@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Box TABLE (i TINYINT, v TINYINT)
INSERT @Box
(
i,
v
)
SELECT i,
v
FROM dbo.fnInitRc4(@Pwd)
DECLARE @Index SMALLINT,
@i SMALLINT,
@j SMALLINT,
@t TINYINT,
@k SMALLINT,
@CipherBy TINYINT,
@Cipher VARCHAR(8000)
SELECT @Index = 1,
@i = 0,
@j = 0,
@Cipher = ''
WHILE @Index <= DATALENGTH(@Text)
BEGIN
SELECT @i = (@i + 1) % 256
SELECT @j = (@j + b.v) % 256
FROM @Box b
WHERE b.i = @i
SELECT @t = v
FROM @Box
WHERE i = @i
UPDATE b
SET b.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
FROM @Box b
WHERE b.i = @i
UPDATE @Box
SET v = @t
WHERE i = @j
SELECT @k = v
FROM @Box
WHERE i = @i
SELECT @k = (@k + v) % 256
FROM @Box
WHERE i = @j
SELECT @k = v
FROM @Box
WHERE i = @k
SELECT @CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
@Cipher = @Cipher + CHAR(@CipherBy)
SELECT @Index = @Index +1
END
RETURN @Cipher
END
-----------------------------------------------------------------------
ALTER FUNCTION [dbo].[DatesBetween](@startDate date, @endDate date)
RETURNS @dates TABLE (
DateValue date NOT NULL
) AS
BEGIN
WHILE (@startDate <= @endDate) BEGIN
INSERT INTO @dates VALUES (@startDate);
SET @startDate = DATEADD(day, 1, @startDate);
END;
RETURN;
END;
-----------------------------------------------------------------------
ALTER function [dbo].[CompairDate](@value date)
returns int
as begin
declare @CDate date
declare @result int
set @CDate=getdate()
if(@value > @CDate)
begin
SET @result=1
end
else if(@value < @CDate)
begin
SET @result=-1
end
else
begin
SET @result=0
end
return @result
end
2.NET Gridview: Fire jQuery Alert When Table Row
3.Select Data Base all primary key
SELECT p.TABLE_NAME,c.CONSTRAINT_NAME,c.COLUMN_NAME ,P.CONSTRAINT_SCHEMA
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = p.TABLE_NAME
AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME
ORDER by c.TABLE_NAME
4.SQL SERVER FUNCTION
Day = DATENAME(DW, Date),
DayOfWeek = DATEPART(WEEKDAY, Date),
DayOfMonth = DAY(Date),
DayOfYear = DATEPART(DY,Date),
PreviousDay = DATEADD(DAY, -1, Date),
NextDay = DATEADD(DAY, 1, Date),
WeekOfYear = DATEPART(WK,Date),
Month = DATENAME(MONTH,Date),
MonthOfYear = MONTH(Date),
QuarterOfYear = DATEPART(Q, Date),
Year = YEAR(Date);
declare @DefaultDateFirst int
set @DefaultDateFirst = @@datefirst
--; 7 First day of week is "Sunday" by default
select [@DefaultDateFirst] = @DefaultDateFirst
set datefirst @DefaultDateFirst
select datename(dw,getdate()) -- Saturday
select datepart(dw,getdate()) -- 7
--; Set the first day of week to * TUESDAY *
--; (some people start their week on Tuesdays...)
set datefirst 2
select datename(dw,getdate()) -- Saturday
--; Returns 5 because Saturday is the 5th day since Tuesday.
--; Tue 1, Wed 2, Th 3, Fri 4, Sat 5
select datepart(dw,getdate()) -- 5 <-- It's not 7!
set datefirst @DefaultDateFirst
select DATEPART(WEEKDAY, GETDATE()) as WeekDayNo,
DATENAME(DW, GETDATE()) as WeekDayName
========================================================================
5. Sql Server function
CREATE FUNCTION AddWorkDays
(
@WorkingDays As Int,
@StartDate AS DateTime
)
RETURNS DateTime
AS
BEGIN
DECLARE @Count AS Int
DECLARE @i As Int
DECLARE @NewDate As DateTime
SET @Count = 0
SET @i = 0
WHILE (@i < @WorkingDays) --runs through the number of days to add
BEGIN
-- increments the count variable
SELECT @Count = @Count + 1
-- increments the i variable
SELECT @i = @i + 1
-- adds the count on to the StartDate and checks if this new date is a Saturday or Sunday
-- if it is a Saturday or Sunday it enters the nested while loop and increments the count variable
WHILE DATEPART(weekday,DATEADD(d, @Count, @StartDate)) IN (1,7)
BEGIN
SELECT @Count = @Count + 1
END
END
-- adds the eventual count on to the Start Date and returns the new date
SELECT @NewDate = DATEADD(d,@Count,@StartDate)
RETURN @NewDate
END
GO
=======================
ALTER FUNCTION [dbo].[AddWorkDays] (@WorkingDays As Int,@StartDate AS DateTime) RETURNS DateTime
AS BEGIN
DECLARE @Count AS Int
DECLARE @i As Int
DECLARE @NewDate As DateTime
DECLARE @CurrentFirstDayInWeek int
DECLARE @DateFirstDifference int
DECLARE @SatNo int
DECLARE @SunNo int
SELECT @CurrentFirstDayInWeek = @@DATEFIRST
SET @DateFirstDifference = 7 - @CurrentFirstDayInWeek
SET @SatNo = 7
SET @SunNo = 1
IF @DateFirstDifference > 0
BEGIN
SET @SatNo = @DateFirstDifference
SET @SunNo = 1 + @DateFirstDifference
END
SET @Count = 0
SET @i = 0
WHILE (@i < @WorkingDays) --runs through the number of days to add
BEGIN
-- increments the count variable
SELECT @Count = @Count + 1
-- increments the i variable
SELECT @i = @i + 1
-- adds the count on to the StartDate and checks if this new date is a Saturday or Sunday
-- if it is a Saturday or Sunday it enters the nested while loop and increments the count variable
WHILE DATEPART(weekday,DATEADD(d, @Count, @StartDate)) IN (@SatNo, @SunNo)
BEGIN
SELECT @Count = @Count + 1
END
END
-- adds the eventual count on to the Start Date and returns the new date
SELECT @NewDate = DATEADD(d,@Count,@StartDate)
RETURN @NewDate
END
========================
1. Email Validation:
This function below basically checks for Nulls, invalid or duplicate characters, and looks for '@' and '.' in the formatting. Once created, you will run the following t-sql:
select dbo.udf_ValidateEmail('myemailaddress@somedomain.com')
Create FUNCTION udf_ValidateEmail (@email varChar(255))
RETURNS bit
AS
begin
return
(
select
Case
When @Email is null then 0 --NULL Email is invalid
When charindex(' ', @email) <> 0 or --Check for invalid character
charindex('/', @email) <> 0 or --Check for invalid character
charindex(':', @email) <> 0 or --Check for invalid character
charindex(';', @email) <> 0 then 0 --Check for invalid character
When len(@Email)-1 <= charindex('.', @Email) then 0--check for '%._' at end of string
When @Email like '%@%@%'or
@Email Not Like '%@%.%' then 0--Check for duplicate @ or invalid format
Else 1
END
)
end
2. Social Security Number - SSN Validation:
We all know that our SSNs are 9-digits long, and follows the standard format of xxx-xx-xxxx. Therefore, we would want to validate that it is in fact 9-digits, the characters are numeric [0-9], and ensure that the format is indeed the right length for each section - ie: 3-2-4. After you create this nice function, you can test it by running various offshoots (try to enter invalid data), but basically looks like:
select dbo.udf_ValidateSSN('123-45-6789')
CREATE FUNCTION udf_ValidateSSN(@SSN varchar(50))
RETURNS BIT
AS
BEGIN
DECLARE @ValidFlag BIT
SET @ValidFlag = 0
IF @SSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
BEGIN
IF LEFT(@SSN,3) != '000' AND SUBSTRING(@SSN,5,2) != '00'
AND RIGHT(@SSN,4) != '0000'
BEGIN
SET @ValidFlag = 1
RETURN ( @ValidFlag)
END
END
IF @SSN LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
BEGIN
IF LEFT(@SSN,3) != '000' AND SUBSTRING(@SSN,4,2) != '00'
AND RIGHT(@SSN,4) != '0000'
BEGIN
SET @ValidFlag = 1
RETURN ( @ValidFlag)
END
END
RETURN ( @ValidFlag)
END
GO
3. Validate/Format Phone Number:
There's always confusion on how to enter a phone number, unless there's a note next to the field that says: "xxx-xxx-xxxx" or "(xxx) xxx-xxxx" or even xxxxxxxxxx. A smart function will take all inputs and simply format it the way the end-user wants it stored in the database. For this we can validate the input, and standardize the format of the entered telephone number. This cool function is a bit more sophisticated, in that it does two things, validate the data; and format it to a standard form. It first checks if the data is numeric, is 10-digits in length, does not have a '1' at the beginning, etc. What it wants the user to enter is, 10-digits, no dashes, and it will format '3124457689' to '312-445-7689'. I'm sure you can modify it to accept the dashes. You can test it out as:
select [dbo].[udf_ValFormatPhone] ('3124445678')
CREATE FUNCTION [dbo].[udf_ValFormatPhone] (@phone NVARCHAR(255))
RETURNS NVARCHAR(255)
AS
BEGIN--function
declare @i int, @repCount int
declare @current_char char(1)
declare @phone_new varchar(50)
set @phone_new = rtrim(ltrim(@phone))
if left(@phone_new, 1) = '1'
set @phone_new = right(@phone_new, len(@phone_new) -1)
set @i = 1
while @i <= len(@phone)
begin
set @repCount = 0
if @i > len(@phone_new)
break
set @current_char = substring(@phone_new, @i, 1)
if isnumeric(@current_char) <> 1
begin
set @repCount = len(@phone_new) - len(replace(@phone_new, @current_char, ''))
set @phone_new = replace(@phone_new, @current_char, '')
end
set @i = @i + 1 - @repCount
end
if isnumeric(@phone_new) = 1 and len(@phone_new) = 10
set @phone_new =
substring(@phone_new, 1,3) + '-' +
substring(@phone_new, 4,3) + '-' +
substring(@phone_new, 7,4)
else
set @phone_new = 'invalid entry'
return @phone_new
END --function
4. Validate URL -
This one is an interesting and neat function, a bit unique, and one that I can source back to and credit to Adam Machanic. (
go
reconfigure
go
sp_configure 'Ole Automation Procedures',1
go
reconfigure
go
And, then, once you create the function, you can run:
SELECT dbo.udf_ValidateURL('http://www.microsoft.com/sql')
---
1 <--if valid will return 1, otherwise 0
CREATE FUNCTION dbo.ValidateURL(@URL VARCHAR(300))
RETURNS BIT
AS BEGIN
DECLARE @Object INT
DECLARE @Return TINYINT
DECLARE @Valid BIT SET @Valid = 0 --default to false
--create the XMLHTTP object
EXEC @Return = sp_oacreate 'MSXML2.ServerXMLHTTP.3.0', @Object OUTPUT
IF @Return = 0
BEGIN
DECLARE @Method VARCHAR(350) --define setTimeouts method --Resolve, Connect, Send, Receive
SET @Method = 'setTimeouts(45000, 45000, 45000, 45000)' --set the timeouts
EXEC @Return = sp_oamethod @Object, @Method
IF @Return = 0
BEGIN --define open method
SET @Method = 'open("GET", "' + @URL + '", false)'--Open the connection
EXEC @Return = sp_oamethod @Object, @Method
END IF @Return = 0
BEGIN --SEND the request
EXEC @Return = sp_oamethod @Object, 'send()'
=====================================================================
private void ShowMessage(int count)
{
StringBuilder sb = new StringBuilder();
sb.Append("");
ClientScript.RegisterStartupScript(this.GetType(),
"script", sb.ToString());
}
=======================================================================
Here is a MIME64 encoder function written entirely in T-SQL
As long as the copyright notice is visible within the function declaration
and you include a note in the documentation of your system that these
functions are written by me, you may use these functions for free of charge.
If you intend to use these functions in a commercial application,
you are required to email me about your system and provide an email address
to the application manager.
If you like the code and use it just for fun, send an e-mail to me
and tell me about your experience with these functions.
I hope you enjoy these functions. I spent about two hours
writing them because I could not find them on the Internet.
CREATE FUNCTION dbo.fnMIME64Encode
(
@RFC3548 BIT = 1,
@PlainText VARCHAR(6000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Characters VARCHAR(64), @Index SMALLINT,
@m1 TINYINT, @m2 TINYINT, @m3 TINYINT, @m4 TINYINT,
@MimeText VARCHAR(8000), @FinalBlock TINYINT
SELECT @Characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
@FinalBlock = DATALENGTH(@PlainText) % 3,
@PlainText = @PlainText + REPLICATE(CHAR(0), (3 - DATALENGTH(@PlainText) % 3) % 3),
@Index = DATALENGTH(@PlainText) - 2,
@MimeText = ''
WHILE @Index > 0
BEGIN
SELECT @m1 = (ASCII(SUBSTRING(@PlainText, @Index, 1)) & 252) / 4,
@m2 = (ASCII(SUBSTRING(@PlainText, @Index, 1)) & 3) * 16 + (ASCII(SUBSTRING(@PlainText, @Index + 1, 1)) & 240) / 16,
@m3 = (ASCII(SUBSTRING(@PlainText, @Index + 1, 1)) & 15) * 4 + (ASCII(SUBSTRING(@PlainText, @Index + 2, 1)) & 192) / 64,
@m4 = ASCII(SUBSTRING(@PlainText, @Index + 2, 1)) & 63
SELECT @MimeText = CASE
WHEN @FinalBlock = 1 THEN SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + '=='
WHEN @FinalBlock = 2 THEN SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + SUBSTRING(@Characters, @m3 + 1, 1) + '='
ELSE SUBSTRING(@Characters, @m1 + 1, 1) + SUBSTRING(@Characters, @m2 + 1, 1) + SUBSTRING(@Characters, @m3 + 1, 1) + SUBSTRING(@Characters, @m4 + 1, 1)
END + @MimeText,
@Index = @Index - 3,
@FinalBlock = 0
END
IF @RFC3548 = 1
BEGIN
SELECT @Index = 1 + DATALENGTH(@MimeText) - DATALENGTH(@MimeText) % 76
IF @Index > DATALENGTH(@MimeText)
SELECT @Index = @Index - 76
WHILE @Index > 1
SELECT @MimeText = STUFF(@MimeText, @Index, 0, CHAR(13) + CHAR(10)),
@Index = @Index - 76
END
RETURN @MimeText
END
and of course the MIME64 decoder function
CREATE FUNCTION dbo.fnMIME64Decode
(
@MimeText VARCHAR(8000)
)
RETURNS VARCHAR(6000)
AS
BEGIN
-- © 2006 Peter Larsson, Developer Workshop, all rights reserved
DECLARE @Characters VARCHAR(64), @Index SMALLINT,
@m1 TINYINT, @m2 TINYINT, @m3 SMALLINT, @m4 SMALLINT,
@p1 TINYINT, @p2 TINYINT, @p3 TINYINT,
@PlainText VARCHAR(6000), @Paddings TINYINT
SELECT @Characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
@MimeText = REPLACE(REPLACE(@MimeText, CHAR(13), ''), CHAR(10), ''),
@Index = DATALENGTH(@MimeText) - 3,
@Paddings = DATALENGTH(@MimeText) - DATALENGTH(REPLACE(@MimeText, '=', '')),
@PlainText = ''
WHILE @Index > 0
SELECT @m1 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
@m2 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 1, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
@m3 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 2, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
@m4 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 3, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,
@p1 = (@m1 & 63) * 4 + (@m2 & 48) / 16,
@p2 = (@m2 & 15) * 16 + (@m3 & 60) / 4,
@p3 = (@m3 & 3) * 64 + (@m4 & 63),
@PlainText = CHAR(@p1) + CHAR(@p2) + CHAR(@p3) + @PlainText,
@Index = @Index - 4
RETURN LEFT(@PlainText, DATALENGTH(@PlainText) - @Paddings)
END
==================================================================
Password Incription
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[fnInitRc4] Script Date: 09/21/2012 20:40:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnInitRc4]
(
@Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS
BEGIN
DECLARE @Key TABLE (i TINYINT, v TINYINT)
DECLARE @Index SMALLINT,
@PwdLen TINYINT
SELECT @Index = 0,
@PwdLen = LEN(@Pwd)
WHILE @Index <= 255
BEGIN
INSERT @Key
(
i,
v
)
VALUES (
@Index,
ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
)
INSERT @Box
(
i,
v
)
VALUES (
@Index,
@Index
)
SELECT @Index = @Index + 1
END
DECLARE @t TINYINT,
@b SMALLINT
SELECT @Index = 0,
@b = 0
WHILE @Index <= 255
BEGIN
SELECT @b = (@b + b.v + k.v) % 256
FROM @Box AS b
INNER JOIN @Key AS k ON k.i = b.i
WHERE b.i = @Index
SELECT @t = v
FROM @Box
WHERE i = @Index
UPDATE b1
SET b1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
FROM @Box b1
WHERE b1.i = @Index
UPDATE @Box
SET v = @t
WHERE i = @b
SELECT @Index = @Index + 1
END
RETURN
END
ALTER FUNCTION [dbo].[fnEncDecRc4]
(
@Pwd VARCHAR(256),
@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Box TABLE (i TINYINT, v TINYINT)
INSERT @Box
(
i,
v
)
SELECT i,
v
FROM dbo.fnInitRc4(@Pwd)
DECLARE @Index SMALLINT,
@i SMALLINT,
@j SMALLINT,
@t TINYINT,
@k SMALLINT,
@CipherBy TINYINT,
@Cipher VARCHAR(8000)
SELECT @Index = 1,
@i = 0,
@j = 0,
@Cipher = ''
WHILE @Index <= DATALENGTH(@Text)
BEGIN
SELECT @i = (@i + 1) % 256
SELECT @j = (@j + b.v) % 256
FROM @Box b
WHERE b.i = @i
SELECT @t = v
FROM @Box
WHERE i = @i
UPDATE b
SET b.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
FROM @Box b
WHERE b.i = @i
UPDATE @Box
SET v = @t
WHERE i = @j
SELECT @k = v
FROM @Box
WHERE i = @i
SELECT @k = (@k + v) % 256
FROM @Box
WHERE i = @j
SELECT @k = v
FROM @Box
WHERE i = @k
SELECT @CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
@Cipher = @Cipher + CHAR(@CipherBy)
SELECT @Index = @Index +1
END
RETURN @Cipher
END
-----------------------------------------------------------------------
ALTER FUNCTION [dbo].[DatesBetween](@startDate date, @endDate date)
RETURNS @dates TABLE (
DateValue date NOT NULL
) AS
BEGIN
WHILE (@startDate <= @endDate) BEGIN
INSERT INTO @dates VALUES (@startDate);
SET @startDate = DATEADD(day, 1, @startDate);
END;
RETURN;
END;
-----------------------------------------------------------------------
ALTER function [dbo].[CompairDate](@value date)
returns int
as begin
declare @CDate date
declare @result int
set @CDate=getdate()
if(@value > @CDate)
begin
SET @result=1
end
else if(@value < @CDate)
begin
SET @result=-1
end
else
begin
SET @result=0
end
return @result
end
This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.
ConversionConversion EmoticonEmoticon