Oracle Database Discussions

I also need to add double-quotes to the beginning and end of a string. For example, the string test should be written as "test" in the column (with double-quotes at the beginning and the end).

The INSERT statement should look like this:

INSERT INTO table (column1) VALUES (???)

Thanks.

If we're talking about prepending 0's, I assume that you just want to convert the number to a string. If you converted the string back to a number, the leading 0's would be lost. If you want to pad a number out to 8 characters with 0's, you would do something like

  1* select to_char( 12345, '00000000' ) from dual
SCOTT @ nx102 JCAVE9420> /

TO_CHAR(1
---------
 00012345

Elapsed: 00:00:00.01

As for your second question, there is nothing special about double-quotes

SCOTT @ nx102 JCAVE9420> create table a( col1 varchar2(10) );

Table created.

Elapsed: 00:00:00.32
SCOTT @ nx102 JCAVE9420> insert into a values( '"test"' );

1 row created.

Elapsed: 00:00:00.01

Justin

Thanks, Justin.

In the first issue, the to_chr works, but it has to be converted back to a number since the column into which it is supposed to go is of type number.

A NUMBER column will not have leading 0's, so your requirements are at odds. You can either have leading 0's and store (or display) the data as a string or you can store (or display) the data as a number and get rid of the leading 0's.

Justin

In reference to the above, I need to store a total of 8 characters in vTgtSALES.EMPLOYEE_ID. For example, if the vTempEmpID variable has 5 characters, I need to add 3 zeros, etc. I have the following code:

vTgtSALES.EMPLOYEE_ID:=CASE Length (LTrim(RTrim(vTempEmpID)))
WHEN 0 THEN WriteLog (pCmdID, pSubCmdID, 'Employee ID value has length of zero. WMC: ' || srcSALESNNM.WMC)
WHEN 1 THEN '0000000' || vTempEmpID
WHEN 2 THEN '000000' || vTempEmpID
WHEN 3 THEN '00000' || vTempEmpID
WHEN 4 THEN '0000' || vTempEmpID
WHEN 5 THEN '000' || vTempEmpID
WHEN 6 THEN '00' || vTempEmpID
WHEN 7 THEN '0' || vTempEmpID WHEN 8 THEN vTempEmpID
ELSE WriteLog (pCmdID, pSubCmdID, 'Employee ID value too long for field. WMC: ' || srcSALESNNM.WMC)
END;

The CASE clause does not seem to work - the actual value of vTempEmpID is stored in vTgtSALES.EMPLOYEE_ID without leading zeros being asses. What am I doing wrong?

Thanks.

...I meant without leading zeros being "ADDED"...

Is EMPLOYEE_ID a NUMBER? Or a VARCHAR2?

As I wrote in my last response

"A NUMBER column will not have leading 0's, so your requirements are at odds. You can either have leading 0's and store (or display) the data as a string or you can store (or display) the data as a number and get rid of the leading 0's."

If EMPLOYEE_ID is a NUMBER, you cannot store leading 0's in it. If it is a VARCHAR2, you should just be using the TO_CHAR function I posted initially rather than using this CASE statement.

Justin

Yes, it is VARCHAR2. I am using the to_char and it works. Thanks!

Out of curiosity, why won't the CASE statement work?