Add 3 new numbers before the already formed number

Question

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.

Answer 1

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.