I need to add the number 188 before the already formed number, for example, there is the number 110 0000 000. And according to the assignment, I need it to be 188 110 0000 000. The most numeric value is varchar, and I thought that it could cost like this.
update myTable1 myTbl
set myTbl.NMBCODE='188'+myTbl.NMBCODE --- insert number characters before the main string variable (both varchar values), concatenation..
But this method is not correct, since the error ORA01722-Invalid Number crashes on +myTbl.TNMBCODE
Please tell me what mechanisms exist to solve this problem.
>Solution :
You are trying to add a new string prefix to an existing string; neither of them are really numbers. So you need to use the string concatenation operator ||, not the arithmetic operator +:
update myTable1 myTbl
set myTbl.NMBCODE = '188 ' || myTbl.NMBCODE
I’ve included a space in the string being prepended, otherwise you would end up with '188110 0000 000' instead of '188 110 0000 000'.
When you use + Oracle tries to convert both strings to numbers to add them together, which isn’t what you want – if it worked at all you’d get 1100000118 – and while it can happily implicitly convert '188' to a number, it fails trying to convert '110 0000 000' because of the spaces.