How to delete data next to Underscore in my sql

Question

Hello Friends I am getting error in delete query could you please help me? my question is - I have land_mark field in that data is like 1251-Peripheral_Rd_2_Blore i want to remove data from _ to next all data like in that filed i need to delete _Rd_2_Blore from land_mark field and i want Answer like 1251-Peripheral Please tell me how to write query for above issue.

Nitin Sontakke · Answer

You can use following query: update table1 set [land_mark] = substring([land_mark] 1 charindex('_' [land_mark]) - 1) where [land_mark] = '1251-Peripheral_Rd_2_Blore' WARNING: PLEASE be extremely CAREFUL before using query in production data. It will UPDATE ALL [land_mark] columns which have given value ('1251-Peripheral_Rd_2_Blore'). Please modify as per your requirement. DO NOT use as it is. Query is provided to you just to give you an idea.

Nitin Sontakke · Answer

Please be very careful with the data. If possible please take the back-up. You can use the following where condition: where [Loc_Landmark] like '%_%'; The above looks for the underscore in the [ Loc_Landmark ] .

Vaishali Uchekar · Answer

Thank you so much for your quick relpy nitin sir I have used below query and it is working fine thank you for that but i want result for all row of Loc_Landmark column in masterlocationid table. Loc_Landmark data is different in column so what should i used in where condition. I want remove all Underscore from Loc_Landmark column. update masterlocationid set Loc_Landmark = substring(Loc_Landmark 1 LOCATE('_' Loc_Landmark) - 1) where Loc_Landmark = 'Peripheral_Rd_2_Blore'