Top community members
All Wiki Articles Create Wiki Article

Have you ever heard about encyclopedia for code?

What about: Wiki for Code project?

* because I've tried to share code and my solutions on different services and it was removed or I was unwelcomed
   - maybe it wasn't good place, but this one is.

if you think you have solution that can help someone, you are welcome to share this solution - Click here

SQL - how to search table by text in MySql / MariaDB

0 contributions
9 points

Using MySQL or MariaDB it is possible to make search query by any column in following ways.

1. Equal operator example

Note: to check how this example works Data base preparation section can be useful. 

1.1. Example 1 - equal operator with exact match:

SELECT * 
FROM `posts`
WHERE `name` = 'JavaScript';

Result:

Equal operator example with MySQL and HeidiSQL
Equal operator example with MySQL and HeidiSQL

1.2. Example 2 - equal operator with exact match:

SELECT * 
FROM `posts`
WHERE `name` = 'JavaScript - how to access input element?';

Result:

Equal operator example with MySQL and HeidiSQL
Equal operator example with MySQL and HeidiSQL

1.3. Example 3 - few equal operators:

SELECT * 
FROM `posts`
WHERE 
		`name` = 'JavaScript - how to access input element?'
	AND
		`content` = 'Post content 2...';

Result:

More complex equal operator example with MySQL and HeidiSQL
More complex equal operator example with MySQL and HeidiSQL

2. Like operator example

Note: to check how this example works Data base preparation section can be useful. 

2.1. Example 1 - like operator with exact match:

SELECT * 
FROM `posts`
WHERE `name` LIKE 'JavaScript';

Result:

Like operator example with MySQL and HeidiSQL
Like operator example with MySQL and HeidiSQL

2.2. Example 2 - like operator with exact left match:

SELECT * 
FROM `posts`
WHERE `name` LIKE 'JavaScript%';

Result:

Like operator example with MySQL and HeidiSQL
Like operator example with MySQL and HeidiSQL

2.3. Example 3 - like operator:

SELECT * 
FROM `posts`
WHERE `name` LIKE '%Script%';

Result:

Like operator example with MySQL and HeidiSQL
Like operator example with MySQL and HeidiSQL

2.4. Example 4 - few like operators:

Note: this approach can be used to find records which contain separated worlds avoiding worlds ordering.

SELECT * 
FROM `posts`
WHERE 
		`name` LIKE '%Script%'
	AND
		`name` LIKE '%how%';

Result:

More complex like operator example with MySQL and HeidiSQL
More complex like operator example with MySQL and HeidiSQL

3. Regex function example

Note: to check how this example works Data base preparation section can be useful. 

3.1. Example 1 - regex operator:

SELECT * 
FROM `posts`
WHERE `name` REGEXP '.*Script.*';

Result:

Regex operator example with MySQL and HeidiSQL
Regex operator example with MySQL and HeidiSQL

3.2. Example 2 - regex operator with few following worlds:

SELECT * 
FROM `posts`
WHERE `name` REGEXP '.*Script.*how.*';

 Result:

More complex regex operator example with MySQL and HeidiSQL
More complex regex operator example with MySQL and HeidiSQL

Note: read more about MySQL REGEXP syntax here.

4. Data base preparation

create_tables.sql file:

CREATE TABLE `posts` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`content` TEXT NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

insert_data.sql file:

INSERT INTO `posts`
	(`id`, `name`, `content`)
VALUES
	(1, 'jQuery - how to get value of input field?', 'Post content 1...'),
	(2, 'JavaScript - how to access input element?', 'Post content 2...'),
	(3, 'JavaScript - how to check type of object?', 'Post content 3...'),
	(4, 'JavaScript - is variable array type?', 'Post content 4...');

 

0 contributions

Checkout latest Findings & News:

Checkout latest questions:

Checkout latest wiki articles:

Hey ūüĎč
Would you like to know what we do?
  • Dirask is IT community, where we share coding knowledge and help each other to solve coding problems.
  • We welcome everyone,
    no matter what the experience,
    no matter how basic the question is,
    this community will help you.
Read more