General discussion
-
CreatorTopic
-
August 17, 1999 at 6:26 am #2080489
Oracle
Lockedby wbenson · about 24 years, 7 months ago
I have a field in my database that is 100 characters long. I want to change characters 50, 51, 52, 53, 54 as follows:
currently: 2345U 7834U
want: 02345 07834Essentially I’m trying to get rid of the end “U” and put a zero on thefront of the number. I need to do this generically as I have about 400 records that require this change. In other words, each of the 400 occurences could use different numbers (though they all end in U and I want to drop the U and put the zero in front).
Topic is locked -
CreatorTopic
All Comments
-
AuthorReplies
-
-
August 17, 1999 at 8:36 pm #3902588
Oracle
by mcristiano · about 24 years, 7 months ago
In reply to Oracle
I need more particular to try to solve your problem. First of all, do you have to use only ORACLE language (like PL-SQL) or other languages like Visual BAsic? 2) The operation is automatically executed or need an user input? What about locking problems?
Bye
MAX-
September 20, 2000 at 7:01 pm #3794335
-
-
August 30, 1999 at 9:18 am #3902812
-
September 20, 2000 at 7:01 pm #3794336
-
-
September 4, 1999 at 1:32 am #3902715
Oracle
by narendra · about 24 years, 7 months ago
In reply to Oracle
Please try the following SELECT query first :
SELECT REPLACE (fieldname, SUBSTR(fieldname, INSTR(fieldname, ‘U’) – 4, 5), ‘0’||RTRIM(SUBSTR(fieldname, INSTR(fieldname, ‘U’) – 4, 5), ‘U’) from TABLENAME;
If the result you get is what you reallywantas the replacement value use it in the UPDATE statement.
The expression is rather complex, I could not simplify it or even try it out on the database as I did not have ORACLE running when I answered this question, so please check the result before applying.
-
September 20, 2000 at 7:01 pm #3794337
-
-
September 8, 1999 at 7:43 pm #3901374
Oracle
by bsadiq · about 24 years, 6 months ago
In reply to Oracle
DECLARE
/* TABLE NAME IS : your_table
COLUMN NAME IS : your_column
Note : Since the column is in CHAR format, it’s length is always 100 */cursor c1 IS
select your_column FROM your_table;
your_var char(100);
begin
for i in c1 loop
/* Checking if the column_name includes the letter ‘u’ or ‘U’ */
IF UPPER(SUBSTR(RTRIM(i.your_column),
LENGTH(RTRIM(i.your_column)),
LENGTH(RTRIM(i.your_column)))) = ‘U’THEN
your_var := ‘0’||replace(upper(i.your_column),’U’);
update your_table set your_column = your_var
where your_column = i.your_column;
commit;
ELSE
/* nothing to update */
NULL;
end if;
end loop;
end;
/-
September 20, 2000 at 7:01 pm #3794338
-
-
September 10, 1999 at 1:50 am #3901356
-
September 20, 2000 at 7:01 pm #3794339
-
-
September 16, 1999 at 2:55 am #3901254
-
September 20, 2000 at 7:01 pm #3794340
-
-
September 21, 1999 at 8:58 am #3901197
Oracle
by pratap_singh · about 24 years, 6 months ago
In reply to Oracle
Use Update statement as follows
for string starting at loc1 and len1 andUpdate table1
set col1=
substr(col1,1,locstart)||’0’||substr(col1,loc1,len1)||
substr(col1,loc1+len1+1)
;-
September 20, 2000 at 7:01 pm #3794341
-
-
March 22, 2000 at 10:13 pm #3901659
-
September 20, 2000 at 7:01 pm #3794342
-
-
July 12, 2000 at 5:39 am #3788998
Oracle
by srishankar.r · about 23 years, 8 months ago
In reply to Oracle
You Can try out the following update statement. But before proceeding try the
select version of the same update statement
or after updation view the record and then commit.Let emp be the Table
EMP
EMP_REG_NO VARCHAR2(100)
update emp set
emp_reg_no = substr(emp_reg_no,1,49)
|| ‘0’
|| substr(emp_reg_no,51,4) ||
substremp_reg_no,56,(length(emp_reg_no) – 56))-
September 20, 2000 at 7:01 pm #3794343
-
-
September 20, 2000 at 7:01 pm #3794334
-
-
AuthorReplies