General discussion

Locked

Oracle

By wbenson ·
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 07834

Essentially 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).

This conversation is currently closed to new comments.

19 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Oracle

by mcristiano 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

Collapse -

Oracle

by wbenson In reply to Oracle

The question was auto-closed by TechRepublic

Collapse -

Oracle

by xx In reply to Oracle

Try using translate function.

Collapse -

Oracle

by wbenson In reply to Oracle

The question was auto-closed by TechRepublic

Collapse -

Oracle

by narendra 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.

Collapse -

Oracle

by wbenson In reply to Oracle

The question was auto-closed by TechRepublic

Collapse -

Oracle

by Bsadiq 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;
/

Collapse -

Oracle

by wbenson In reply to Oracle

The question was auto-closed by TechRepublic

Collapse -

Oracle

by cdungey In reply to Oracle

Hope this helps..
Index you values in and let the sample code filter..
If InStr(Text1, "U") Then
Text2 = "0" & Mid(Text1, 1, InStr(Text1, "U") - 1)
Else
Text2 = Text1
End If

Collapse -

Oracle

by wbenson In reply to Oracle

The question was auto-closed by TechRepublic

Back to Web Development Forum
19 total posts (Page 1 of 2)   01 | 02   Next

Software Forums