CODEWITHSUNDEEP
mysql
Mech
Mech

Reputation: 4015

MySQL asterisk in query

UPDATE phpn_banned_ip 
SET ip_address = SUBSTRING(ip_address, 1, LENGTH(ip_address)-3)&"\*" 
where id=2

When running this query in mysql, it zeros out the ip address. Any ideas on how to make the last character an asterisk (*)?

Upvotes: 0

Views: 361

Answers (2)

John Ruddell
John Ruddell

Reputation: 25862

SUBSTRING(ip_address, 1, LENGTH(ip_address)-3)&"\*" 
----------------------------------------------^---

that is invalid syntax you want to use concat

CONCAT(SUBSTRING(ip_address, 1, LENGTH(ip_address)-3), "*")

so your whole query would look something like this

UPDATE phpn_banned_ip 
SET ip_address = CONCAT(SUBSTRING(ip_address, 1, LENGTH(ip_address)-3), "*") 
where id=2

Upvotes: 0

tadman
tadman

Reputation: 211670

If you're trying to concatenate strings:

CONCAT(SUBSTRING(ip_address, 1, LENGTH(ip_address)-3), "*")

Upvotes: 1

Related Questions