Alter data in table to add a charachter?

Question

I'm trying to alter a table to add the charachter X to every item in a certain row in my table.

I need to trunk all transaction’s in the table under a field to 40 characters, this needs to be done using a batch executed DML statement. The 40th character should be set to a X – to show its been shortened.

Currently I am trying to use SUBSTR just to trial a way of updating the lines

Note: On the below example is just some trial and error attempts, I created a sample table in SQL fiddle as I'm not allowed to update on main table yet

UPDATE test SET tester = SUBSTR(tester , 40, 1) + 'x';

However this returens NULL on all instances

I have tried

UPDATE test SET tester = tester + SUBSTR(tester , 40, 1) + 'x';

However this (when building schem in fiddler) returns

Schema Creation Failed: ORA-01722: invalid number

I have tried browsing through google and oracle data dictionary, but tbh my researching skills is pants, any ideas where I can look specifically?

I located this but I'm having trouble implementing

Any ideas would help.

Thanks

Moe · Accepted Answer · 2014-07-29T11:34:00.783

1

I have this working:

UPDATE test SET tester = SUBSTR(tester,1,40) || 'X';

Comedy error using + instead of ||

Can't believe it took me nearly 1 hr to figure this out! Live'n'learn I guess! :)

edited Jul 29 '14 at 11:34

answered Jul 29 '14 at 10:59

Moe

61
6

score 1 · Answer 2 · answered Jul 29 '14 at 11:02

The concatenation operator is ||, not +. You also have your substring arguments the wrong way round; and you probably only want to apply this to values that are longer than 40 characters to start with:

UPDATE test
SET tester = SUBSTR(tester, 1, 39) || 'X'
WHERE LENGTH(tester) > 40;

This will set the 40th character to X only if it had to be shortened. (Although of course, if you have an original value that happened to be 40 characters long and already ended in X you wouldn't be able to tell whether it had been truncated.)

With starting data:

SELECT tester, LENGTH(tester) FROM test;

TESTER                                             LENGTH(TESTER)
-------------------------------------------------- --------------
abcdefghijklmnopqrstuvwxy 39 char value                        39 
abcdefghijklmnopqrstuvwxyz 40 char value                       40 
abcdefghijklmnopqrstuvwxyza 41 char value                      41 
abcdefghijklmnopqrstuvwxyzab 42 char value                     42 
abcdefghijklmnopqrstuvwxyzabcdefghij 50 char value             50

That updates three rows and leaves you with:

SELECT tester, LENGTH(tester) FROM test;

TESTER                                             LENGTH(TESTER)
-------------------------------------------------- --------------
abcdefghijklmnopqrstuvwxy 39 char value                        39 
abcdefghijklmnopqrstuvwxyz 40 char value                       40 
abcdefghijklmnopqrstuvwxyza 41 char valX                       40 
abcdefghijklmnopqrstuvwxyzab 42 char vaX                       40 
abcdefghijklmnopqrstuvwxyzabcdefghij 50X                       40

Alter data in table to add a charachter?

2 Answers2