RE: SQL question du jour

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