user3441816 user3441816 - 7 months ago 10
SQL Question

Deleting particular string value from column from table

If my column contains these values

"Added by Manu on 31-12-2013 & 12thFeb2014"
"Added by Manu on 01-01-2014 [nx-RP-S]"
"Added by Manu on 8thApril2014 (Pnxs)"
added by Manu on 7thFeb2014 [np1PUNCTnp2]


then I want a query which delete only this string

"Added by Manu on 31-12-2013 & 12thFeb2014"`
"Added by Manu on 01-01-2014 ""
"Added by Manu on 8thApril2014"
"added by Manu on 7thFeb2014 " rest [nx-RP-S],(Pnxs),[np1PUNCTnp2]


respectively will as it is in column.

Example if original value is
"Added by Manu on 01-01-2014 [nx-RP-S]"
then I want to delete only
"Added by Manu on 01-01-2014"
from column, and if original value is
"Added by Manu on 31-12-2013 & 01-01-2014"
then make it
null


Thanks in advance.

Answer

this will remove the part from [

 select SUBSTRING_INDEX(your_column, '[', 1) as removedPart from table1

EDIT: for you corrected requirement try this:

 select CASE WHEN your_column NOT Like '%[%' then 'NULL'

 else SUBSTRING_INDEX(your_column, ' ', -1)  end as modified from table1

Demo

EDIT2:

for the update statment use this:

  Update table1 
  set your_column = 
  CASE WHEN your_column NOT Like '%[%' then 'NULL'
  else SUBSTRING_INDEX(your_column, ' ', -1)  end

DEMO