Question 216958 in Mysql Question posted in
The official MySQL documentation can be found here.

How to do custom search in mysql?

I have one address table in that postcode column is there let me explain the scenarios
For example:- 523272 records are there

  1. if i search with 5232 i don’t want to get results.
  2. if i search with 523272(Exact match) i need to get results.
  3. if i search with 5232725656 Now also i need to fetch results related to 523272 records (starts equals to condition).

i tried for this one wildcard entries and length it’s not working,can you help me to achieve this scenario.

SELECT * FROM WHERE postcode LIKE '%523272';
Tags:

3

Answers


  1. 0

    You should reverse the condition. Append the wildcard to the column value, and match the search string against that pattern.

    SELECT * 
FROM tablename
WHERE '523272' LIKE CONCAT(postcode, '%')

    DEMO

    Note that this can’t be indexed, so it will be slow on a large table.

    Login or Signup to reply.
  2. 0

    You can try this I have tested all possible values and @post is a variable in which you can set your value

    Ex :

    set @post=523;

select * 
from address 
where @post like CONCAT('%',postcode,'%') or postcode like CONCAT('%',@post,'%')
        or  @post like CONCAT(postcode,'%') or postcode like CONCAT(@post,'%')
        or  @post like CONCAT('%',postcode) or postcode like CONCAT('%',@post);
    Login or Signup to reply.
  3. 0

    Probably you should use Regex_Like operator along with Substring and RPAD. Following might work:

    SELECT * FROM WHERE REGEX_LIKE (postcode, concat(RPAD(SUBSTRING('SEARCHSTRING', 1, 6), 6, '?'), '.+'));

    if SEARCHSTRING = 5232, the regex expression becomes 5232??.+
if SEARCHSTRING = 523234, the regex expression becomes 523234.+
if SEARCHSTRING = 523234123, the regex expression becomes 523234.+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search