Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: String validation

    I need to make sure that a string contains at least one number, at least one letter and has no repeating chars. Anyone done something similar? What's the best way to do it? Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's a nice one...but what do you mean at least 1 number and at least 1 alpha...what would the rest of the chars be? special chars?

    I give it a wack though....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    Hi... I mean that it can't be all alpha or all numeric. They have to mix it up. Thanks.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    oh ok, that's a little different...

    What about special chars (!@#$%^&*), they allowed?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sounds like you are verifying a password?

    You (or Brett, cause he is such a nice guy) will need a function that steps through the string checking each of the conditions. Store the success of the conditions in bit variables and then return the bitwise AND result of the three variables at the end of the function.

    At least that's what I've done in the past. Maybe brett has a different plan of attack. I can't come up with an "elegant" solution at this point, but implementing this logic will allow you to easily add or remove criteria in the future.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Posts
    7
    You are correct - I'm verifying a password and special chars are allowed (but not required).

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Sounds like you are verifying a password?

    You (or Brett, cause he is such a nice guy) will need a function that steps through the string checking each of the conditions. Store the success of the conditions in bit variables and then return the bitwise AND result of the three variables at the end of the function.

    At least that's what I've done in the past. Maybe brett has a different plan of attack. I can't come up with an "elegant" solution at this point, but implementing this logic will allow you to easily add or remove criteria in the future.
    Yeah, No kidding...I was going for a non iterative one...

    Why don't you post the function you wrote....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No repeating chars?

    Why?

    That'll be harder

    Code:
    DECLARE @x varchar(10)
    
     SELECT @x = 'A234567890'
       SELECT 1 WHERE ISNUMERIC(@x)=1
    UNION ALL
       SELECT 1 
        WHERE @x LIKE '[^0-9]'
    
     SELECT @x = '1234567890'
       SELECT 1 WHERE ISNUMERIC(@x)=1
    UNION ALL
       SELECT 1 
        WHERE @x LIKE '[^0-9]'
    
     SELECT @x = '!@#$%^&*()_+'
       SELECT 1 WHERE ISNUMERIC(@x)=1
    UNION ALL
       SELECT 1 
        WHERE @x LIKE '[^0-9]'
    
     SELECT @x = 'ABCDEFGHIJ'
       SELECT 1 WHERE ISNUMERIC(@x)=1
    UNION ALL
       SELECT 1 
        WHERE @x LIKE '[^0-9]'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    My $.02:

    Code:
    CREATE FUNCTION fnIsComplex (
    	@sStringIn varchar(255)
    )
    
    RETURNS bit
    
    AS
    
    BEGIN
    	DECLARE
    		@iLength int,
    		@iCounter int,
    		@sChar char(1),
    		@bAlpha bit,
    		@bNumber bit
    	
    	SELECT @iCounter = 0
    	SELECT @iLength = LEN(@sStringIn)
    	SELECT @bAlpha = 0
    	SELECT @bNumber = 0
    	
    	-- first Check for Alpha
    	WHILE @iCounter <= @iLength
    	BEGIN
    		SELECT @sChar = substring(@sStringIn, @iCounter, 1)
    	
    		IF CHARINDEX(@sChar, 'abcdefghijklmnopqrstuvwxyz') > 0
    			BEGIN
    				SELECT @bAlpha = 1
    				BREAK
    			END
    	   	IF @iCounter > @iLength
    	      		BREAK
    	   	ELSE
    			SELECT @iCounter = @iCounter + 1
    	      		CONTINUE
    	END
    
    	-- Reset counter
    	SELECT @iCounter = 0
    	
    	-- then Check for Numberic
    	WHILE @iCounter <= @iLength
    	BEGIN
    		SELECT @sChar = substring(@sStringIn, @iCounter, 1)
    	
    		IF CHARINDEX(@sChar, '1234567890') > 0
    			BEGIN
    				SELECT @bNumber = 1
    				BREAK
    			END
    	   	IF @iCounter > @iLength
    	      		BREAK
    	   	ELSE
    			SELECT @iCounter = @iCounter + 1
    	      		CONTINUE
    	END
    	
    	RETURN @bAlpha & @bNumber
    
    END
    
    -- Example:
    SELECT dbo.fnIsComplex('1234')			-- = 0
    SELECT dbo.fnIsComplex('MyPassword')		-- = 0
    SELECT dbo.fnIsComplex('1MyPassword34')		-- = 1
    Oooops, I missed the part about no repeating characters....sigh.

    Regards,

    hmscott
    Last edited by hmscott; 02-27-04 at 16:34.

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    What function are you talking about !!! I think that this guy needs a . check constraint using udf.

    Code:
    use pubs 
    go
    Create FUNCTION [udf_PwdCheck] (@pwd varchar(40))
    RETURNS INT
    AS
    BEGIN
    DECLARE @return_value bit
    if (@pwd like '%[1-9]%' and @pwd like '%[a-z]%')
    	set @return_value = 1
    else
    	set @return_value = 0
    return @return_value
    END
    go
    
    CREATE TABLE [user_master] (
    [userid] [varchar] (20)NULL ,
    [pwd] [varchar] (40) check (dbo.udf_PwdCheck(pwd) =1) 
    ) ON [PRIMARY]
    GO
    
    insert into user_master  select 'Enigma','I am the 1'
    insert into user_master  select 'Brett','Bring on the Margaritas!!'
    
    go
    drop table user_master
    drop function udf_PwdCheck
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Dec 2002
    Posts
    1,245
    So much more elegant. I was looking for the %[0-9]%' thing, but I couldn't remember what function it went with.

    sigh again.

    Regards,

    hmscott

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Fine, Fine, Fine...No Margaritas for me...

    Ya big bully


    Originally posted by Enigma
    What function are you talking about !!! I think that this guy needs a . check constraint using udf.

    Code:
    use pubs 
    go
    Create FUNCTION [udf_PwdCheck] (@pwd varchar(40))
    RETURNS INT
    AS
    BEGIN
    DECLARE @return_value bit
    if (@pwd like '%[1-9]%' and @pwd like '%[a-z]%')
    	set @return_value = 1
    else
    	set @return_value = 0
    return @return_value
    END
    go
    
    CREATE TABLE [user_master] (
    [userid] [varchar] (20)NULL ,
    [pwd] [varchar] (40) check (dbo.udf_PwdCheck(pwd) =1) 
    ) ON [PRIMARY]
    GO
    
    insert into user_master  select 'Enigma','I am the 1'
    insert into user_master  select 'Brett','Bring on the Margaritas!!'
    
    go
    drop table user_master
    drop function udf_PwdCheck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    EDIT: But you missed the non dup char requirement...

    Never seen one like that for a pwd before...doesn't that limit your possible combinations?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Enigma, you need to add a repeating character validation. I can't think of an elegant way to do that without stepping through the string.

    I think I have my old code at home, so if nobody posts a full answer then I'll take a shot at adapting mine this evening. This isn't complex, so I'd encourage rebam to give it try.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Anyone think there is a way to do this with math?

    Like SUM(ASCII(@x))....soemthing something something...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •