check for numeric in db2 sql

nagammais · #1 (**permalink**) 11-22-05, 12:36

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.

Wim · #2 (**permalink**) 11-22-05, 12:46

Read this previous post

miguelajh · #3 (**permalink**) 01-05-12, 10:42

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)

tonkuma · #4 (**permalink**) 01-05-12, 11:37

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' , ' ') = ''

fengsun2 · #5 (**permalink**) 01-05-12, 20:48

I tried this ,but it seems results are not right.....

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

tonkuma · #6 (**permalink**) 01-05-12, 21:08

You must forgot a leading blank in third parameter of TRANSLATE function in my Example 1.

fengsun2 · #7 (**permalink**) 01-05-12, 21:29

Sorry , i missed it ...
but i think a trim is still needed for your Example 1。。。

tonkuma · #8 (**permalink**) 01-05-12, 21:30

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.

tonkuma · #9 (**permalink**) 01-05-12, 21:40

Quote:

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.

fengsun2 · #10 (**permalink**) 01-05-12, 22:03

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。

tonkuma · #11 (**permalink**) 01-05-12, 22:19

Quote:

..., 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".

Quote:

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.

fengsun2 · #12 (**permalink**) 01-05-12, 22:27

Ah， you are right。There is no need to have a trim on it.
I learned much from this thread. thx

tonkuma · #13 (**permalink**) 01-05-12, 22:41

I want to note one more.

Quote:

... 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.

Internet Services

Web Development

Digital Marketing

Consumer Tech