CODEWITHSUNDEEP
sqloracle-database
enemyofnone
enemyofnone

Reputation: 85

Oracle Update table to set specific attribute value in semicolon separated values

I have a column where I have values like:

Email_Password+oi8hu907b;New_eMail+Y;Email_Username+iugbhijhb8

Now I want to update New_eMail attribute for all rows which has Y to N without affecting anything else.

Please advise.

Upvotes: 1

Views: 500

Answers (3)

Maulik Shah
Maulik Shah

Reputation: 402

My answer is a slight improvement over the answer from user davegreen100 Since they don't allow me to post it as a comment, I add it here.

update <<tablename>>
  set <<columnname>> = replace(<<columnname>>,';New_eMail+Y;',';New_eMail+N;')
where <<columnname>> like '%;New_eMail+Y;%'

Upvotes: 1

Rene
Rene

Reputation: 10541

Since it may be the only place in the string where '+Y;' occurs the following statement may do the trick:

update <your_table> 
  set <your_column> = replace(<your_column>,'+Y;','+N;')
  where instr(<your_column>,'+Y;')>0

This solution differs from the others provided because it does not depend on the value of the email address.

Upvotes: 1

davegreen100
davegreen100

Reputation: 2115

i hate it but...

update table
   set column = replace(column,'New_eMail+Y','New_eMail+N')
where column like '%New_eMail+Y%'

you don't need the WHERE clause but if you put a functional index on the table it may be quicker with it

Upvotes: 2

Related Questions