How to leave search field prepared to search for any term

Question

I have a register of textures where the code is composed of numbers and sometimes with letters, for example:

Código: 2564
Código: C-6551

I would like to have the search field prepared to perform the search for any term searched by the user, such as:

User searches by code 6551 the search return C-6551, tried to make a search using the REPLACE but it didn’t go very well, what I did was this:

SELECT *
  FROM cor_textura
 WHERE REPLACE(REPLACE(REPLACE(codigo, '.', ''), '-', ''), ' ', '') =
       REPLACE(REPLACE(REPLACE('".$edBusca."', '.', ''), '-', ''), ' ', '')
   AND cor_textura.status = 1

I tried to replace the term search by changing ponto for espaço, traço for espaço and espaço for espaço.

The database I’m using is phpMyAdmin and the table structure is this:

`cor_textura` (
  `id_cor_textura` int(10) NOT NULL AUTO_INCREMENT,
  `codigo` varchar(150) NOT NULL,
  `img` int(10) NOT NULL,
  `status` tinyint(5) NOT NULL,
  PRIMARY KEY (`id_cor_textura`)
)

Like I said, I couldn’t solve my problem.

Answer 1

If you use the command LIKE from which you search any part of the text?

SELECT *
  FROM cor_textura
 WHERE codigo like '%6651%'
   AND cor_textura.status = 1

I made a example in Sqlfindle, using Mysql, see if that’s what.

The got the return:

| id | codigo | status |
|----|--------|--------|
|  3 | C-6551 |      1 |