T sql Code Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

T sql Code Help

Author

Topic

raju123
Starting Member

1 Post

Posted - 2010-12-03 : 11:33:20

I have a requirement to create a User Defined Function (UDF) for PIN that can accommodate the following 3 rules

1.Pin Should Contain number (0-9) characters (a-z,A-Z), and a limited number of special characters as following:
~ ! $ # ^ * _ = { } [ ] : ? . , (space is not an allowed character)

2.Pin Should be case-sensitive

3. Pin Should Include at least one letter and one number

Table Structure :

CREATE TABLE [dbo].[pin_sample]
( [Pin] [nvarchar] (50) NULL )

Sample Data:

Insert Into [pin_sample] (Pin)

SELECT 'Moghaffar82!'union all
SELECT 'SEMOrana1999'union all
SELECT 'Seham011'union all
SELECT 'InAsq8MO'union all
SELECT 'Ziklin567'union all
SELECT 'Yusuf123'union all
SELECT 'hardstuFF12'union all
SELECT 'Rxfv1p13'union all
SELECT 'A#10frica'union all
SELECT 'Waeeed1974'

-- Rule #3

DECLARE @pin nvarchar (50), @error varchar (500)

IF patindex('%[a-z]%',@Pin) > 0 and patindex('%[0-9]%',@Pin) > 0
BEGIN
Select 'Pin has atleast one charecter and one number'
END
ELSE
Select @error = 'Invalid Pin'

I need T sql Codes for Rule#1 and Rule#2

Your Help will be greatly appreciated..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 11:40:15

1 something like
not like '%[^~!$#\^*_={}\[\]:?.,0-9a-zA-Z]%' escape '\'

2. That's to do with the checking not the pin itself.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources