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

Mysql – how to ignore space in sql search query?

I have a SQL table which has a column entry of an IP Address like 5. 5. 44. 4 .
That is 5.<space><space>5.<space>44.<space><space>4

I have a query to search for this ip address like

select dns_name from toms_table where ip_address like '%5.5.44.4%'

But this is not listing the dns_name only because of the space in ipaddress.

How can indicate in sql query to ignore the space in table entries while searching ?

Tags:

2

Answers


  1. 0

    You need to remove all characters that you don’t need or want with REPLACE

    If the ip adress only consist of ‘5. 5. 44. 4’ with out additional characters, you don’t need the %, mysql wouldn’t use the index on that table, which would increase the speed of the query

    CREATE tABLE toms_table (ip_address varchar(100), dns_name varchar(4))

    INSERT INTO toms_table VALUES ('5.  5. 44.  4','test')

    select dns_name from toms_table where REPLACE(ip_address, ' ','') like '%5.5.44.4%'
    dns_name
    test 
    select dns_name from toms_table where REPLACE(ip_address, ' ','') like '5.5.44.4'
    dns_name
    test

    fiddle

    Login or Signup to reply.
  2. 0 
    REGEXP '\b5\s*.\s*5\s*.\s*44\s*.\s*4\b'

    (That syntax is for MySQL 8.0; a different syntax is needed for MySQL 5 and MariaDB.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search