Re: SQL question du jour

[Bill Brosch <bbrosch@xxxxxxxxxxxxxx>]

trim() it inside the character_length function

Norbut, Jim wrote:
> Have a field that is 512 characters long.
>
> How do I find out how many characters are actually used thought.
>
>  
>
> CREATE TABLE LAW8EXTN/CHECKOUT)
>
>      TRANS_NBR       CHAR(10)       NOT NULL WITH DEFAULT,
>
>      CHECK_DATE      DATE           NOT NULL WITH DEFAULT,
>
>      VENDOR          CHAR(9)        NOT NULL WITH DEFAULT,
>
>      VENDOR_VNAME    CHAR(50)       NOT NULL WITH DEFAULT,
>
>      BASE_CHK_AMT    NUMERIC(15,2)  NOT NULL WITH DEFAULT,
>
>      INVOICES        CHAR (512)     NOT NULL WITH DEFAULT);
>
>                                                                  
>
> SELECT character_length(invoices) FROM law8extn/checkout
>
>  
>
> Gives me 
>
>  
>
> CHARACTER_LENGTH
>
>             512 
>
>             512 
>
>             512 
>
>             512 
>
>             512 
>
>             512 
>
>             512 
>
>  
>
> And so on and so on.....but I know for a fact that not all 512
> characters are used.
>
>  
>
> SELECT invoices FROM law8extn/checkout
>
>  
>
>  INVOICES                   
>
>  200512:200512              
>
>  3631                       
>
>  ADV302007                  
>
>  77751                      
>
>  5335250:F0008520           
>
>  110405:113005              
>
>  47285:44512                
>
>  3351:3352:3353:3354:3355   
>
>  14564743:14505256          
>
>  311335:311060              
>
>  140                        
>
>  2005120105                 
>
>  932444                     
>
>  6001607910                 
>
>  519979                     
>
>  121605                     
>
>  121605                     
>
>  
>
>