CODEWITHSUNDEEP
mysql
hexedecimal
hexedecimal

Reputation: 299

Extracting data from column and put them in another column

I'm having a problem with building a query. What I have is this in THE db

--------------------------------------
 Id |name  | profilenr  |  nr     
--------------------------------------
  1 | Harry| admin-124  | NULL 
  2 | Barry| admin-267  | NULL
  6 | gerry| user-689   | NULL
  9 | larry| user-435   | NULL

What I want to do is: Getting only the numbers from the profilenr column and put them in the nr column of each profile that starts whit admin- . In this example only for harry 124 in colum nr And for Barry only 267 in colum nr. I know this is possible but don't know how to build the query for this.

Upvotes: 0

Views: 56

Answers (2)

Monika X
Monika X

Reputation: 342

May be more like this:

update my_table update
set nr = substr(profilenr, locate('-', profilenr)+1, 3)
where profilenr like 'admin-%';

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You can use a substr and locate 

update my_table 
set nr = substr(profilenr, locate('-',profilenr)+1, 3);

Upvotes: 1

Related Questions