Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: Built table problem

    Hi.

    I am a beginner of SQL server.
    I have a table namely Customer with a column "CustomerName" with data type varchar and length 50.
    Column Name-Customer Name
    Data Type- varchar
    Length- 50.

    May I know how to ENSURE that every customer's name only contain ALPHABET and strictly avoid the numeric character and
    other characters such as "&, !,#....".

    Please help. Thank you.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    See the BOL for Check Constraints !!!
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this idea?

    create table test(id int,
    code varchar(50)
    CONSTRAINT mycheck check(code+replicate('Z',50-datalength(code)) like replicate('[A-Z]',50) or
    code+replicate('Z',50-datalength(code)) like replicate('[a-z]',50)
    ))

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Very Nice

    Code:
    INSERT INTO test(code) SELECT 'ABC'
    INSERT INTO test(code) SELECT '123'
    INSERT INTO test(code) SELECT 'AB.'
    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
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    [explosion]

    Code:
    INSERT INTO test(code) SELECT 'Brett Kaiser'
    [/explosion]
    Get yourself a copy of the The Holy Book

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

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Enigma
    [explosion]

    Code:
    INSERT INTO test(code) SELECT 'Brett Kaiser'
    [/explosion]
    Now it is even more simple:

    drop table test
    go
    create table test(id int,
    code varchar(50)
    CONSTRAINT mycheck check(lower(replace(code,' ','Z')+replicate('Z',50-datalength(code))) like replicate('[a-z]',50)
    )
    )
    go
    INSERT INTO test(code) SELECT 'Brett Kaiser'

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    That looks cool !!!
    Get yourself a copy of the The Holy Book

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

Posting Permissions

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