Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2005
    Posts
    1

    Unanswered: check for numeric in db2 sql

    Hi,

    I need to check the value of a field which is a varchar type is numeric or whether it includes alpha characters in db2 sql.
    Can any one pls help out.


    This works in mysql not in DB2

    select * from policy where
    polnumber like ('%[A-Z]%')



    Thanks.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2012
    Posts
    1

    A middle solution

    Hello.
    also i have a problem like yours.

    this is my solution temporal, that i read in other forum in another way. it, uses a case, i don't use a case.

    select * from esquema1.tabla1 where campo1 is not null
    and (substring(campo1 ,1,1) in ('1','2','3','4','5','6','7','8','9','0') and
    substring(campo1 ,2,1) in ('1','2','3','4','5','6','7','8','9','0') and
    substring(campo1 ,3,1) in ('1','2','3','4','5','6','7','8','9','0') and
    substring(campo1 ,4,1) in ('1','2','3','4','5','6','7','8','9','0') and
    substring(campo1 ,5,1) in ('1','2','3','4','5','6','7','8','9','0') and
    substring(campo1 ,6,1) in ('1','2','3','4','5','6','7','8','9','0')
    )

    Campo1 is varchar(6)

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This thread is very old, and some links are not accessible now.
    So, it would be better to open a new thread.

    Anyway, please try...

    Example 1:
    Code:
    SELECT *
     FROM  esquema1.tabla1
     WHERE TRANSLATE(campo1 , '*' , ' 0123456789' , ' ') = ''

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    I tried this ,but it seems results are not right.....

    values TRANSLATE('1234','*','0123456789',' ') = ' ' ( 4 blanks )
    values TRANSLATE('01234','*','0123456789',' ') = '* ' ( 1 * + 4 blanks)

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You must forgot a leading blank in third parameter of TRANSLATE function in my Example 1.

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    Sorry , i missed it ...
    but i think a trim is still needed for your Example 1。。。

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is the result of a small test.
    Note: fourth parameter of TRANSLATE function was not necessary.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT campo1
         , TRANSLATE(campo1 , '*' , ' 0123456789') AS verifyed
         , TRANSLATE(campo1 , '*' , '0123456789')  AS verify_error
     FROM  (VALUES '1234'
                 , '01234'
                 , '012 34'
                 , '012abc'
                 , '2325128192'
           ) table1(campo1)
    ;
    ------------------------------------------------------------------------------
    
    CAMPO1     VERIFYED   VERIFY_ERROR
    ---------- ---------- ------------
    1234                              
    01234                 *           
    012 34        *       *           
    012abc        abc     *  abc      
    2325128192                        
    
      5 record(s) selected.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    but i think a trim is still needed for your Example 1。。。
    Do you mean that you allow leading and trailing blanks?
    If so, you are right.

    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT polnumber
         , TRANSLATE(polnumber , '*' , ' 0123456789')       AS not_leading_trailing_blanks
         , TRANSLATE(TRIM(polnumber) , '*' , ' 0123456789') AS ok_leading_trailing_blanks
     FROM  (VALUES '1234'
                 , '01234'
                 , '012 34'
                 , '012abc'
                 , '2325128192'
                 , '  01234'
                 , '01234 '
                 , ' 01234 '
           ) policy(polnumber)
    ;
    ------------------------------------------------------------------------------
    
    POLNUMBER  NOT_LEADING_TRAILING_BLANKS OK_LEADING_TRAILING_BLANKS
    ---------- --------------------------- --------------------------
    1234                                                             
    01234                                                            
    012 34        *                           *                      
    012abc        abc                         abc                    
    2325128192                                                       
      01234    **                                                    
    01234           *                                                
     01234     *     *                                               
    
      8 record(s) selected.

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    nope, i think
    trim must bracket the translate, like this :

    SELECT * FROM esquema1.tabla1
    WHERE trim(TRANSLATE(campo1 , '*' , ' 0123456789' , ' ')) = ''

    as i metioned above ,if campo1 is numberic string, afer translate it will be a all blanks string which length equals to the the original string compo1. it cant not ='' if there is no trim。

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ..., afer translate it will be a all blanks string which length equals to the the original string compo1. it cant not ='' if there is no trim。
    See String comparisons in manual "SQL Reference".
    String comparisons

    ...

    When comparing character strings of unequal lengths, the comparison is made
    using a logical copy of the shorter string, which is padded on the right with blanks
    sufficient to extend its length to that of the longer string. This logical extension is
    done for all character strings, including those tagged as FOR BIT DATA.
    Here is an example.

    Example 3:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT polnumber
         , CASE TRANSLATE(polnumber , '*' , ' 0123456789')
           WHEN '' THEN 'Valid.'
           ELSE         'Not valid.'
           END  AS judge
     FROM  (VALUES '1234'
                 , '01234'
                 , '012 34'
                 , '012abc'
                 , '2325128192'
                 , '  01234'
                 , '01234 '
                 , ' 01234 '
           ) policy(polnumber)
    ;
    ------------------------------------------------------------------------------
    
    POLNUMBER  JUDGE     
    ---------- ----------
    1234       Valid.    
    01234      Valid.    
    012 34     Not valid.
    012abc     Not valid.
    2325128192 Valid.    
      01234    Not valid.
    01234      Not valid.
     01234     Not valid.
    
      8 record(s) selected.

  12. #12
    Join Date
    Nov 2011
    Posts
    334
    Ah, you are right。There is no need to have a trim on it.
    I learned much from this thread. thx

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to note one more.

    ... which is padded on the right with blanks
    sufficient to extend its length to that of the longer string. This logical extension is
    done for all character strings, including those tagged as FOR BIT DATA.
    Even if the string was tagged as FOR BIT DATA,
    it would be padded with blanks(not x'00' sometimes expected for bit data).

    Example 4a: Unequal.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT 'Comparison of BIT DATA with unequl length' title
         , CASE CAST(x'0000' AS CHAR(2) FOR BIT DATA)
           WHEN CAST(x'00'   AS CHAR(1) FOR BIT DATA) THEN
                'Equal'
           ELSE 'Not equal'
           END  AS judge
     FROM  sysibm.sysdummy1
    ;
    ------------------------------------------------------------------------------
    
    TITLE                                     JUDGE    
    ----------------------------------------- ---------
    Comparison of BIT DATA with unequl length Not equal
    
      1 record(s) selected.
    Example 4b: paddid with x'00'
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT 'Comparison of BIT DATA with unequl length' title
         , CASE CAST(x'310000' AS CHAR(3) FOR BIT DATA)
           WHEN RPAD( CAST(x'31'     AS CHAR(1) FOR BIT DATA)
                    , 3
                    , x'00' ) THEN
                'Equal'
           ELSE 'Not equal'
           END  AS judge
     FROM  sysibm.sysdummy1
    ;
    ------------------------------------------------------------------------------
    
    TITLE                                     JUDGE    
    ----------------------------------------- ---------
    Comparison of BIT DATA with unequl length Equal    
    
      1 record(s) selected.
    or

    Example 4c: TRIM trailing x'00'
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT 'Comparison of BIT DATA with unequl length' title
         , CASE TRIM( T x'00' FROM CAST(x'310000' AS CHAR(3) FOR BIT DATA) )
           WHEN CAST(x'31'     AS CHAR(1) FOR BIT DATA) THEN
                'Equal'
           ELSE 'Not equal'
           END  AS judge
     FROM  sysibm.sysdummy1
    ;
    ------------------------------------------------------------------------------
    
    TITLE                                     JUDGE    
    ----------------------------------------- ---------
    Comparison of BIT DATA with unequl length Equal    
    
      1 record(s) selected.
    Last edited by tonkuma; 01-06-12 at 00:02. Reason: Add Example 4c. Add Example 4b. Add an Example.

Posting Permissions

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