Professional Web Applications Themes

Function to test alphanumeric - IBM DB2

Anyone have a function or method (that could be converted to a function) whereby one could test or check a CHAR or VARCHAR column so that it contains only alphanumeric characters - similar to the way COBOL checks. I want to make sure a column contains only the characters 'A' through 'Z', 'a' through 'z', '0' through '9', and space. Example: alphanumeric('Dan Blocker') would be true alphanumeric('Dan, Bloc-er') would be false Ultimately, I'd like to apply this as a CHECK constraint. Thanks. fprose at supreme dot sp dot state dot az dot us...

  1. #1

    Default Function to test alphanumeric

    Anyone have a function or method (that could be converted to a
    function) whereby one could test or check a CHAR or VARCHAR column so
    that it contains only alphanumeric characters - similar to the way
    COBOL checks.

    I want to make sure a column contains only the characters 'A' through
    'Z', 'a' through 'z', '0' through '9', and space.

    Example: alphanumeric('Dan Blocker') would be true
    alphanumeric('Dan, Bloc-er') would be false

    Ultimately, I'd like to apply this as a CHECK constraint.

    Thanks.


    fprose at supreme dot sp dot state dot az dot us
    Fred Prose Guest

  2. #2

    Default Re: Function to test alphanumeric

    Wasn't Dan Blocker "Hoss" on Bonanza?

    Anyway, the thread below shows how to use the ASCII scalar function to
    test for numeric. It could be extended to alpha as well:

    [url]http://groups.google.com/groups?q=%22is+numeric%22+group:comp.databases.ibm-db2+group:comp.databases.ibm-db2&hl=en&lr=&ie=UTF-8&group=comp.databases.ibm-db2&selm=OFE6525217.D7C348FC-ON86256999.002DE4DC%40INSITUFORM.COM&rnum=1[/url]

    Fred Prose wrote:
    > Anyone have a function or method (that could be converted to a
    > function) whereby one could test or check a CHAR or VARCHAR column so
    > that it contains only alphanumeric characters - similar to the way
    > COBOL checks.
    >
    > I want to make sure a column contains only the characters 'A' through
    > 'Z', 'a' through 'z', '0' through '9', and space.
    >
    > Example: alphanumeric('Dan Blocker') would be true
    > alphanumeric('Dan, Bloc-er') would be false
    >
    > Ultimately, I'd like to apply this as a CHECK constraint.
    >
    > Thanks.
    >
    >
    > fprose at supreme dot sp dot state dot az dot us
    Blair Adamache Guest

  3. #3

    Default Re: Function to test alphanumeric

    "Fred Prose" <fprosehotmail.com> wrote in message
    news:195a4770.0307071237.4ad2cc76posting.google.c om...
    > Anyone have a function or method (that could be converted to a
    > function) whereby one could test or check a CHAR or VARCHAR column so
    > that it contains only alphanumeric characters - similar to the way
    > COBOL checks.
    >
    > I want to make sure a column contains only the characters 'A' through
    > 'Z', 'a' through 'z', '0' through '9', and space.
    >
    > Example: alphanumeric('Dan Blocker') would be true
    > alphanumeric('Dan, Bloc-er') would be false
    >
    > Ultimately, I'd like to apply this as a CHECK constraint.
    >
    > Thanks.
    Try

    CHECK (
    TRANSLATE
    (
    RTRIM(name)
    , ''
    , 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz0123456789'
    , 'X'
    ) = REPEAT('X', LENGTH(name))
    )


    Of course want we could probably really do with is regular expressions

    Regards
    Paul Vernon
    Business Intelligence, IBM Global Services


    Paul Vernon Guest

  4. #4

    Default Re: Function to test alphanumeric

    Another idea to work on

    WITH TABA(C) AS
    (
    values (translate(upper('abc123'), '',
    'ABCDEFGHIJKLMNOPQRSTUVQXYZ0123456789', ('0') ) )
    )
    SELECT CASE C WHEN (REPEAT( '0', LENGTH(C))) THEN (0) ELSE (1) END FROM TABA

    I also support this statement
    >Either you write an external function, which will most probably the best
    >performance-wise, or you do something like this (untested):
    PM


    PM \(pm3iinc-nospam\) Guest

  5. #5

    Default Re: Function to test alphanumeric

    Fred Prose wrote:
    > Anyone have a function or method (that could be converted to a
    > function) whereby one could test or check a CHAR or VARCHAR column so
    > that it contains only alphanumeric characters - similar to the way
    > COBOL checks.
    >
    > I want to make sure a column contains only the characters 'A' through
    > 'Z', 'a' through 'z', '0' through '9', and space.
    >
    > Example: alphanumeric('Dan Blocker') would be true
    > alphanumeric('Dan, Bloc-er') would be false
    >
    CREATE FUNCTION AlphaNumeric (inStr VARCHAR(4000) )
    RETURNS CHAR(1)
    LANGUAGE SQL
    CONTAINS SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    RETURN
    CASE
    WHEN TRANSLATE(inStr, '',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz0123456789') = ''
    THEN 'T'
    ELSE 'F'
    END
    ;
    Tokunaga T. Guest

  6. #6

    Default Re: Function to test alphanumeric

    Thanks to all who have responded. You have provided several good solutions.
    Fred Prose Guest

Similar Threads

  1. Alphanumeric Expression
    By meiko in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 5th, 06:04 PM
  2. [cfc] A way to test a if a function exists ?
    By obouillaud in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: April 13th, 02:32 PM
  3. alphanumeric randomized image
    By Duncan Hill in forum PHP Development
    Replies: 2
    Last Post: September 25th, 11:15 PM
  4. Alphanumeric sort
    By JoeElla in forum Microsoft Access
    Replies: 5
    Last Post: August 5th, 08:03 PM
  5. Forcing Alphanumeric Text Entry
    By sekdab in forum PERL Miscellaneous
    Replies: 6
    Last Post: July 18th, 11:07 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139