CODEWITHSUNDEEP
sqlsql-servert-sql
Sal
Sal

Reputation: 305

Updating data in a table

I have a unique issue. In a table called CRIMINAL sits data of criminal orientation in a column called CHARGE1. But sometimes there is extra data within the table that I'd like to delete. Basically more than one charge, on occasion, sits in the column such as below:

speeding 75/45 #2 trespassing #3 burglary

etc, etc. Some only have one charge, but many have multiple .

What I would like it to delete everything to the right of, and including #2, so the CHARGE1 table would only have speeding 75/45

There are literally tens of thousands of rows so fixing manually is not an option :( Thank you in advance

Upvotes: 0

Views: 37

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use replace in combination with substring and update the column.

update criminals set charge1 = 
replace(charge1,substring(charge1, charindex('#',charge1), len(charge1)),'')
where charindex('#',charge1) > 0

Upvotes: 0

Rahul
Rahul

Reputation: 77866

You can use string functions and perform a UPDATE operation like below

update criminal 
set charge = substring(charge,1, charindex('#', charge))
where some_column = filter;

Include a WHERE condition in case you want to update to a specific row. If all the records CHARGE column have same data format then without WHERE clause as well should work fine.

Upvotes: 1

Related Questions