Loading

How to delete data next to Underscore in my sql

How to delete data next to Underscore in my sql

Vaishali Uchekar

9y785SQL

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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted 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 Sontakke9y ago
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 Uchekar9y ago
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'