Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Unanswered: Restrict data to alpha in alphanumeric field

    I have an Access 2002 front end and have upsized my tables into SQL server 2003. My problem is that I have an alphanumeric field that the company wants the data restricted to alpha characters only. I believe that I need to set a check constraint on this field in the table. However, the field can have up to 24 characters and I do not know how to enter a check constraint that allows up to 24 characters of alpha only.

    Any help here is appreciated!

    Thanks,
    B&R

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could do this as part of the controls validation
    or you can do this as part of the SQL server tabel defintition

    If your constraint was define in the Access/JET tabel then it should have been carried accross to SQL server.

    if you want to go down the constraint route it would be best to ask this in the SQL server thread, rather than the Access thread
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would vote table definition and I would also vote to move the thread to SQL Server. I've answered one of B&Rs in there already.

    Anyhoo - how's about this 'un?
    http://weblogs.sqlteam.com/jeffs/arc...nstraints.aspx

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think a CHECK Constraint using a LIKE is just what you need.
    Code:
    DROP TABLE #t1630804
    GO
    CREATE TABLE #t1630804 ( -- Create scratch table
       myColumn	  VARCHAR(24)	 NOT NULL
       )
    
    INSERT INTO #t1630804 (  --  Insert some test data, successfully
       myColumn
       ) SELECT 'foo'
       UNION SELECT 'Bar64ack'
       UNION SELECT 'bat erk'
       UNION SELECT 'baz_oops'
       UNION SELECT 'PatP'
    
    SELECT *  --  Show offending data
       FROM #t1630804
       WHERE myColumn LIKE '%[^A-Za-z]%'
    
    UPDATE #t1630804  --  Kluge offending data
       SET myColumn = 'plugh'
          WHERE myColumn LIKE '%[^A-Za-z]%'
    
    ALTER TABLE #t1630804  -- Add constraint
       ADD CONSTRAINT XC01t1630804
          CHECK (myColumn NOT LIKE '%[^A-Za-z]%')
    
    INSERT INTO #t1630804 (  --  Now it fails!
       myColumn
       ) SELECT 'foo'
       UNION SELECT 'Bar64ack'
       UNION SELECT 'bat erk'
       UNION SELECT 'baz_oops'
       UNION SELECT 'PatP'
    -PatP

  5. #5
    Join Date
    Feb 2004
    Posts
    139
    Thanks for all the help! This works.

    B&R

Posting Permissions

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