RE: SQL question du jour

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

In this case, your field is CHAR NOT NULL WITH DEFAULT, which makes this field fixed-length, initialized to blanks.  Your field length will always be 512 bytes.

You can trim blanks from the right, so that you can see "length of character data", using LENGTH(RTRIM(invoices)), but this is probably not what you really want....

In this case, you might be better off making invoices VARCHAR, so that you can write the trimmed value to the database, and know the actual length of field data.  CHAR will always pad data to fill the field....

hth,
Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Norbut, Jim
Sent: Monday, January 07, 2008 10:45 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL question du jour


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