0

I have a password field (char type).

How can i make sure it contains exectly 8 chars between a-z and 0-9 only?

What kind of check() should i use?

Ty!

user3885474
  • 381
  • 2
  • 4
  • 12

1 Answers1

3

Not sure it's a great idea to do this but try something along the lines of this:

DECLARE @test VARCHAR(10) = '123a*dzx' 

SELECT 1 
WHERE  Len(@test) = 8 
       AND @test NOT IN (SELECT @test 
                         WHERE  @test LIKE '%[^a-z0-9]%')

You can try it out on SQL Fiddle

Gidil
  • 4,137
  • 2
  • 34
  • 50
  • 1
    The method seems fine although the `AND ...` part could be simplified [like this](http://sqlfiddle.com/#!3/d41d8/37225): `AND @test NOT LIKE '%[^a-z0-9]%'`. – Andriy M Jul 30 '14 at 06:46
  • Ty. I'm new in Sql. Does it make sense like this: diver_password char(8) not null check(Len(diver_password) = 8 AND diver_password NOT IN (SELECT diver_password WHERE @test LIKE '%[^a-z0-9]%')) – user3885474 Jul 30 '14 at 07:34
  • @AndriyM Or something like this: diver_password char(8) not null check(Len(diver_password) = 8 AND diver_password NOT IN ('%[^a-z0-9]%')), – user3885474 Jul 30 '14 at 08:06
  • @user3885474: No, I don't believe `NOT IN ('%[^a-z0-9]%')` would work actually. That would be same as `<> '%[^a-z0-9]%'`, which would be literal matching rather than pattern matching. You do need `NOT LIKE` there. – Andriy M Jul 30 '14 at 09:11
  • @AndriyM so the correct syntax is: diver_password char(8) not null check(Len(diver_password) = 8 AND diver_password not like('%[^a-z0-9]%')) – user3885474 Jul 30 '14 at 09:35