CODEWITHSUNDEEP
mysqlmysql-error-1064mysql-error-1093
user3649154
user3649154

Reputation: 21

how to cut the strings before a particular strings in sql

Device_Users

  1. R90FDJMVAdministrator
  2. PG04373Administrator

I only want administrator in this field what update query should I run

Upvotes: 0

Views: 80

Answers (2)

James Z
James Z

Reputation: 12317

Maybe this is good enough:

update Device_Users 
set field = 'Administrator' 
where field like '%Administrator'

Upvotes: 0

JeromeE
JeromeE

Reputation: 469

You can use CHARINDEX practical because you can set a starting point for the search in the string, useful if you know how long is your prefix (I have put 6 but it depends on your data)

UPDATE table SET column = 'Administrator' WHERE CHARINDEX('Administrator',column,6) > 0

If needed you can refine the WHERE clause to make sure you're not replacing entries which have something after Administrator

AND LEN(column) = CHARINDEX('Administrator',column,6) + LEN('Administrator')

Upvotes: 0

Related Questions