Add 3 new numbers before the already formed number-CodePudding

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.

CodePudding user response：

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.