|
|
SQL Server T-SQL User-Defined Function of the Week
Volume 1 Number 40 August 19, 2003
Are any of these characters in that string?
Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm
To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
Volume 1 Number 40 August 19, 2003
The online version of this newsletter is available here:
http://www.novickSoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-40-udf_Txt_HasCharInGroup.htm
+---------------------------------------------------------------+
Happy Summer from Novick Software www.NovickSoftware.com
+---------------------------------------------------------------+
Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
Check out the UDF Frequently Asked Questions at:
http://www.NovickSoftware.com/UDFofWeek/UDF_FAQ.htm
To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
Visit http://www.NovickSoftware.com for additional material
about SQL Server, Visual Basic, ASP, XML, and security.
+---------------------------------------------------------------+
I've been working on security a lot the last couple of weeks.
One of the SQL Server Best Practices is to use only Windows
Authentication for your SQL server. But it isn't always
possible.
Some of the common reasons that require the use of mixed
authentication are:
- Non-Windows clients
- Windows-98/ME users who don't sign in
- 3rd part applications that require mixed mode.
So if you're stuck with mixed mode, make the best of it. A
Best Practice for mixed mode is to require strong passwords.
Next week's UDF is going to test a password for a list of
criteria such as:
- Minimum Length
- Contains alpha, numeric, and special characters.
And I might think of some other rules along the way.
As I began writing the code to check the rule "contains at least
one of this list of characters" the code looked useful enough
to isolate into a UDF. After all, some other situation might
come up where I need a similar function.
This week's UDF, udf_Txt_HasCharInGroup, checks a string for the
presence of at lest one of a list of characters. Here's the
CREATE FUNCTION script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_TXT_HasCharInGroup (
@sInput varchar(8000) -- string to find char in
, @sGroup varchar(255) -- Characters to find
) RETURNS BIT -- 1 if any char in @sGroup is in @sInput
WITH SCHEMABINDING
/*
* Determines if any character in @sGroup is present in
* @sInput. If @sGroup is a consecutive range such as
* 0-9 then use the LIKE operator with a search for %[0-9]%
* instead of this UDF.
*
select dbo.udf_TXT_HasCharInGroup ('mypwd123abcd', '13579')
*
* Test:
PRINT 'Test 1 ' + CASE WHEN 1 =
dbo.udf_TXT_HasCharInGroup ('mypwd123abcd', '13579')
THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 2 ' + CASE WHEN 0 =
dbo.udf_TXT_HasCharInGroup ('mypwd24', '13579')
THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 3 ' + CASE WHEN 0 =
dbo.udf_TXT_HasCharInGroup ('mypwd123abcd', '''[]#$%')
THEN 'Worked' ELSE 'ERROR' END
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as the T-SQL UDF of the Week Vol 1 #40 8/19/03
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN
DECLARE @nPos int -- position in @sInput
, @nNumChars int -- Character to find this time
SELECT @nNumChars = LEN(@sInput)
, @nPos = 1
WHILE @nPos <= @nNumChars BEGIN
IF CHARINDEX(SUBSTRING(@sGroup, @nPos, 1)
, @sInput -- string to search
)
> 0 RETURN 1 -- Found one. We're done
SET @nPos = @nPos + 1
END -- WHILE
RETURN 0 -- Didn't find any
END
GO
GRANT EXEC on [dbo].[udf_TXT_HasCharInGroup] to PUBLIC
GO
\-------Stop copying above this line ---------------------------/
If you know that the characters that you're searching for are in
a consecutive range of characters, for example 0-9, the LIKE
operator with an argument such as '%[0-9]%' does the same job
in an expression instead of a UDF. udf_TXT_HasCharInGroup is
most useful when you either don't know the list of characters
to search for in advance or when the characters aren't in a
range.
Here are some examples of how to use the new UDF:
/------- Start copying below this line -------------------------\
SELECT dbo.udf_TXT_HasCharInGroup ('mypwd123abcd', '13579')
as [Has Odd]
, dbo.udf_TXT_HasCharInGroup ('mypwd24', '13579')
as [No Odd]
, dbo.udf_TXT_HasCharInGroup ('mypwd123abcd$', '''[]#$%')
as [$ at end]
, dbo.udf_TXT_HasCharInGroup ('#mypwd123abcd', '''[]#$%')
as [# at start]
go
\-------Stop copying above this line ---------------------------/
(Results)
Has Odd No Odd $ at end # at start
------- ------ -------- ----------
1 0 1 1
Next week I'll show you the UDF to check the passwords. However,
by the time I complete it, I may have written
udf_TXT_HasCharInGroup out of that function. We'll see.
+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share? Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
+--------------------------------------------------------------+
|
|
