CODEWITHSUNDEEP
oracle-databasesetsubstr
Ivano
Ivano

Reputation: 11

Using substring in SQL update statement - oracle

I'm writing a SQL query that updates just only one field of a string (tax_cd) but it doesn't work, I must replace field 12 which is '0' or blank with '1',character 0 is the one you found in the condition where, all other characters in the string should not be changed

TAX_CD is a VARCHAR2(26)

UPDATE doc_table
SET tax_cd=substr(tax_cd,1,11)||'1'||substr(tax_cd,13,26)
WHERE substr(tax_cd,12,1) = 0 ;

Current : "ABC 0 0 L K "

Updated : "ABC 0 1 L K "

any advice?

the string is not always equal as length, for example:

it is written on the table like this

"0 K 1"

so the ideal is to read the record field with a select, make a length and based on that try to write in position 12 the code 1 but I do not know how to do it

Lenght(tax_cd) Count(*)

Upvotes: 1

Views: 239

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

To me, it looks like

SQL> with test (tax_cd) as
  2    (select 'ABC 0      0      L K     ' from dual union all
  3     select 'DEF 0             L K     ' from dual union all
  4     select 'XYZ 7      2      F M     ' from dual
  5    )
  6  select tax_cd,
  7    case when substr(tax_cd, 12, 1) in ('0', ' ') then
  8              substr(tax_cd, 1, 11) || '1' || substr(tax_cd, 13)
  9         else tax_cd
 10    end as new_value
 11  from test;

TAX_CD                     NEW_VALUE
-------------------------- ------------------------------
ABC 0      0      L K      ABC 0      1      L K
DEF 0             L K      DEF 0      1      L K
XYZ 7      2      F M      XYZ 7      2      F M

SQL>

so you'd

update doc_table set
  tax_cd = substr(tax_cd, 1, 11) || '1' || substr(tax_cd, 13)
  where substr(tax_cd, 12, 1) in ('0', ' ');

Upvotes: 0

Related Questions