CODEWITHSUNDEEP
phpmysql
helljav
helljav

Reputation: 3

Replace numeric values for *'s in mysql

HI I'm really new to programming (noob), as such I need to change de values from one colum to another in a MySQL database. Here some info: database: project, table: users, column2: number1, column3: number2. In number1 I have numeric data in these two forms: 91234567 or 1000123456 I want to change them to this form: 91aaaa67 or 91 * * * * 67 and 1000aaaa56or change the a's for *'s and put them in number2. The table has several users and the numbers are totally different for each user. This means, that in both cases I want to change the third to sixth digits from right to left in the numbers from column2 (number1) and store them in column3 (number2). I have tried the following code:

UPDATE users SET number2 = REPLACE( number2,  '91234567',  '91****67' )

I tried the code from Andriy M in this link But it doesn't work for me, there are erros that appear regarding the RowSetToUpdate function and I think it's beacuase it is for SQL not MySQL.

So I don't know how to do it and make it automated, I also have a php document with forms that fill the database correctly. I just want to add this function to the database!!! I use phpmyadmin on a windows environment, php 5.4.7, mysql 5.5.27.

I hope you can help me, and thank you in advance!!!

Upvotes: 0

Views: 135

Answers (2)

juergen d
juergen d

Reputation: 204746

UPDATE users 
SET number2 = concat(left(number2, 2), '****', right(number2, 2))

SQLFiddle demo

Upvotes: 1

AwokeKnowing
AwokeKnowing

Reputation: 8206

you should be able to do it like this

UPDATE users SET number2= CONCAT(LEFT(number1,2),'aaaa',RIGHT(number1,2)) WHERE LENGTH(number1) =8

and

UPDATE users SET number2= CONCAT(LEFT(number1,4),'aaaa',RIGHT(number1,2)) WHERE LENGTH(number1) =10

Upvotes: 0

Related Questions