Styjun

Where to disclose a zero day vulnerability

Sample Inverse Color in Photoshop

Resume: How to quantify my contributions as a software engineer?

Does a gnoll speak both Gnoll and Abyssal, or is Gnoll a dialect of Abyssal?

How can I discourage sharing internal API keys within a company?

How to say "quirky" in German without sounding derogatory?

Mean π: Archimedes vs. Gauss - π computation through generalized means

Should I leave the first authorship of our paper to the student who did the project whereas I solved it?

Is there an inconsistency about Natasha Romanoff's middle name in the MCU?

In Germany, how can I maximize the impact of my charitable donations?

Where can I find vomiting people?

Can I toggle Do Not Disturb on/off on my Mac as easily as I can on my iPhone?

Why did they ever make smaller than full-frame sensors?

Does an oscilloscope subtract voltages as phasors?

Why does Coq include let-expressions in its core language

Is there a real-world mythological counterpart to WoW's "kill your gods for power" theme?

What is this unknown executable on my boot volume? Is it Malicious?

"Literally" Vs "In the true sense of the word"

Parallel resistance in electric circuits

Is there a reliable way to hide/convey a message in vocal expressions (speech, song,...)

How do email clients "send later" without storing a password?

Can I disable a battery powered device by reversing half of its batteries?

Have there been any countries that voted themselves out of existence?

Where does the expression "triple-A" come from?

Get records “Jone Deo” or “Deo Jone” in mysql

Should I use the datetime or timestamp data type in MySQL?Find duplicate records in MySQLHow to do a regular expression replace in MySQL?How to get a list of user accounts using the command line in MySQL?Retrieving the last record in each group - MySQLHow do I get a YouTube video thumbnail from the YouTube API?How do I add indices to MySQL tables?Reference — What does this symbol mean in PHP?How to get the sizes of the tables of a MySQL database?How do I import an SQL file using the command line in MySQL?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

3

How can we get the result of "Jone Deo" or "Deo Jone" in mysql?
Example.

Table Name students

ID------Name

1-------Jone Deo

2-------Bill Gates

Now, if someone enter Deo Jone or Jone Deo then Jone Deo records will be search out in mysql

I've search box in my website. If someone search "Jone Deo" or "Deo Jone", I will need only result from above tables.
I don't want to use PHP loop or explode function.

php mysql

share|improve this question

edited Mar 28 at 9:54

Darshak DRC

asked Mar 28 at 9:46

Darshak DRCDarshak DRC

8088 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  any sample or attempted code?
  
  – Jah
  Mar 28 at 9:50
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Jah Actually, I want only mysql query. I searched a lot but I did not get any results. Hence, I posted questions here. :)
  
  – Darshak DRC
  Mar 28 at 9:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Entered how? Are you trying to query this database with any combination of strings delimited with a space? Any other logic you want to apply?
  
  – tshimkus
  Mar 28 at 9:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @tshimkus, I just edited my questions. I 've search box in my website. If someone search "Jone Deo" or "Deo Jone", I will need only result from above tables. I don't want to use PHP loop or explode function. for get this results.
  
  – Darshak DRC
  Mar 28 at 10:00
  

  
  
  
  

add a comment
 | 

3

How can we get the result of "Jone Deo" or "Deo Jone" in mysql?
Example.

Table Name students

ID------Name

1-------Jone Deo

2-------Bill Gates

Now, if someone enter Deo Jone or Jone Deo then Jone Deo records will be search out in mysql

I've search box in my website. If someone search "Jone Deo" or "Deo Jone", I will need only result from above tables.
I don't want to use PHP loop or explode function.

php mysql

share|improve this question

edited Mar 28 at 9:54

Darshak DRC

asked Mar 28 at 9:46

Darshak DRCDarshak DRC

8088 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  any sample or attempted code?
  
  – Jah
  Mar 28 at 9:50
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Jah Actually, I want only mysql query. I searched a lot but I did not get any results. Hence, I posted questions here. :)
  
  – Darshak DRC
  Mar 28 at 9:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Entered how? Are you trying to query this database with any combination of strings delimited with a space? Any other logic you want to apply?
  
  – tshimkus
  Mar 28 at 9:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @tshimkus, I just edited my questions. I 've search box in my website. If someone search "Jone Deo" or "Deo Jone", I will need only result from above tables. I don't want to use PHP loop or explode function. for get this results.
  
  – Darshak DRC
  Mar 28 at 10:00
  

  
  
  
  

add a comment
 | 

How can we get the result of "Jone Deo" or "Deo Jone" in mysql?
Example.

Table Name students

ID------Name

1-------Jone Deo

2-------Bill Gates

Now, if someone enter Deo Jone or Jone Deo then Jone Deo records will be search out in mysql

I've search box in my website. If someone search "Jone Deo" or "Deo Jone", I will need only result from above tables.
I don't want to use PHP loop or explode function.

php mysql

share|improve this question

edited Mar 28 at 9:54

Darshak DRC

asked Mar 28 at 9:46

Darshak DRCDarshak DRC

8088 bronze badges

share|improve this question

edited Mar 28 at 9:54

Darshak DRC

asked Mar 28 at 9:46

Darshak DRCDarshak DRC

8088 bronze badges

share|improve this question

edited Mar 28 at 9:54

Darshak DRC

asked Mar 28 at 9:46

Darshak DRCDarshak DRC

8088 bronze badges

asked Mar 28 at 9:46

Darshak DRCDarshak DRC

8088 bronze badges

asked Mar 28 at 9:46

Darshak DRCDarshak DRC

8088 bronze badges

3 Answers
3

active

oldest

votes

3

You might want to try Full-text search:

 SELECT * FROM People WHERE MATCH (full_name)
 AGAINST ('+Doe +John' IN BOOLEAN MODE);

http://sqlfiddle.com/#!9/d21d4e/5

Full-text search explained: https://www.w3resource.com/mysql/mysql-full-text-search-functions.php

"Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row."

So in short, to answer your question you should see an improvement in query execution times by implementing a full text index on wide VARCHAR columns. Providing you are using a compatible storage engine ( InnoDB or MyISAM)

share|improve this answer

edited Mar 28 at 10:23

answered Mar 28 at 10:13

JahJah

68544 silver badges2121 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Will Full Text search slow our search process ?
  
  – Darshak DRC
  Mar 28 at 10:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Darshak DRC, check my updated answer.
  
  – Jah
  Mar 28 at 10:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It is working in my case. Thank you so much for your wonderful help. :)
  
  – Darshak DRC
  Mar 28 at 11:01
  

  
  
  
  

add a comment
 | 

1

We can use REGEXP here:

SELECT ID, Name
FROM yourTable
WHERE Name REGEXP '[[:<:]]Jone[[:>:]]' AND REGEXP '[[:<:]]Deo[[:>:]]';

This would also handle the case where you just wanted to assert that Jone and Deo appear as standalone words anywhere in the name, even if the name had e.g. a middle component.

share|improve this answer

answered Mar 28 at 9:51

Tim BiegeleisenTim Biegeleisen

272k1414 gold badges120120 silver badges182182 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  But how can I split the words ? Is this possible without PHP's explod or loop ?
  
  – Darshak DRC
  Mar 28 at 10:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you mean by "split the words?"
  
  – Tim Biegeleisen
  Mar 28 at 10:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In search box, users will enter "John Deo" together. As per you solution need to explode/split the whole word into 2 words.
  
  – Darshak DRC
  Mar 28 at 10:05
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Use PHP's split function? It is up to you how you separate the terms entered. My answer (and your original question) have to do with what query you could run given those two separate terms, not how to obtain them from a PHP script.
  
  – Tim Biegeleisen
  Mar 28 at 10:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Actually, I don't need to use split/explod or any loop of PHP. Is this possible using mysql query?
  
  – Darshak DRC
  Mar 28 at 10:08
  

  
  
  
  

 | 
show 1 more comment

0

If you can append % at the beginning and end of each word of the string, you can use the following query:-

SELECT * FROM `table` WHERE 
name 
LIKE 
(SELECT SUBSTRING_INDEX("%Jone% %Deo%", " ", -1)) 
OR 
(SELECT SUBSTRING_INDEX("%Jone% %Deo%", " ", 1))

Tested and working for both Deo Jone and Jone Deo.

share|improve this answer

answered Mar 28 at 10:45

Rakesh JakharRakesh Jakhar

4,89422 gold badges77 silver badges1818 bronze badges

add a comment
 | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55394504%2fget-records-jone-deo-or-deo-jone-in-mysql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

You might want to try Full-text search:

 SELECT * FROM People WHERE MATCH (full_name)
 AGAINST ('+Doe +John' IN BOOLEAN MODE);

http://sqlfiddle.com/#!9/d21d4e/5

Full-text search explained: https://www.w3resource.com/mysql/mysql-full-text-search-functions.php

"Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row."

So in short, to answer your question you should see an improvement in query execution times by implementing a full text index on wide VARCHAR columns. Providing you are using a compatible storage engine ( InnoDB or MyISAM)

share|improve this answer

edited Mar 28 at 10:23

answered Mar 28 at 10:13

JahJah

68544 silver badges2121 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Will Full Text search slow our search process ?
  
  – Darshak DRC
  Mar 28 at 10:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Darshak DRC, check my updated answer.
  
  – Jah
  Mar 28 at 10:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It is working in my case. Thank you so much for your wonderful help. :)
  
  – Darshak DRC
  Mar 28 at 11:01
  

  
  
  
  

add a comment
 | 

3

You might want to try Full-text search:

 SELECT * FROM People WHERE MATCH (full_name)
 AGAINST ('+Doe +John' IN BOOLEAN MODE);

http://sqlfiddle.com/#!9/d21d4e/5

Full-text search explained: https://www.w3resource.com/mysql/mysql-full-text-search-functions.php

"Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row."

So in short, to answer your question you should see an improvement in query execution times by implementing a full text index on wide VARCHAR columns. Providing you are using a compatible storage engine ( InnoDB or MyISAM)

share|improve this answer

edited Mar 28 at 10:23

answered Mar 28 at 10:13

JahJah

68544 silver badges2121 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Will Full Text search slow our search process ?
  
  – Darshak DRC
  Mar 28 at 10:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Darshak DRC, check my updated answer.
  
  – Jah
  Mar 28 at 10:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It is working in my case. Thank you so much for your wonderful help. :)
  
  – Darshak DRC
  Mar 28 at 11:01
  

  
  
  
  

add a comment
 | 

You might want to try Full-text search:

 SELECT * FROM People WHERE MATCH (full_name)
 AGAINST ('+Doe +John' IN BOOLEAN MODE);

http://sqlfiddle.com/#!9/d21d4e/5

Full-text search explained: https://www.w3resource.com/mysql/mysql-full-text-search-functions.php

"Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row."

So in short, to answer your question you should see an improvement in query execution times by implementing a full text index on wide VARCHAR columns. Providing you are using a compatible storage engine ( InnoDB or MyISAM)

share|improve this answer

edited Mar 28 at 10:23

answered Mar 28 at 10:13

JahJah

68544 silver badges2121 bronze badges

 SELECT * FROM People WHERE MATCH (full_name)
 AGAINST ('+Doe +John' IN BOOLEAN MODE);

share|improve this answer

edited Mar 28 at 10:23

answered Mar 28 at 10:13

JahJah

68544 silver badges2121 bronze badges

answered Mar 28 at 10:13

JahJah

68544 silver badges2121 bronze badges

answered Mar 28 at 10:13

JahJah

68544 silver badges2121 bronze badges

1

We can use REGEXP here:

SELECT ID, Name
FROM yourTable
WHERE Name REGEXP '[[:<:]]Jone[[:>:]]' AND REGEXP '[[:<:]]Deo[[:>:]]';

This would also handle the case where you just wanted to assert that Jone and Deo appear as standalone words anywhere in the name, even if the name had e.g. a middle component.

share|improve this answer

answered Mar 28 at 9:51

Tim BiegeleisenTim Biegeleisen

272k1414 gold badges120120 silver badges182182 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  But how can I split the words ? Is this possible without PHP's explod or loop ?
  
  – Darshak DRC
  Mar 28 at 10:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you mean by "split the words?"
  
  – Tim Biegeleisen
  Mar 28 at 10:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In search box, users will enter "John Deo" together. As per you solution need to explode/split the whole word into 2 words.
  
  – Darshak DRC
  Mar 28 at 10:05
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Use PHP's split function? It is up to you how you separate the terms entered. My answer (and your original question) have to do with what query you could run given those two separate terms, not how to obtain them from a PHP script.
  
  – Tim Biegeleisen
  Mar 28 at 10:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Actually, I don't need to use split/explod or any loop of PHP. Is this possible using mysql query?
  
  – Darshak DRC
  Mar 28 at 10:08
  

  
  
  
  

 | 
show 1 more comment

1

We can use REGEXP here:

SELECT ID, Name
FROM yourTable
WHERE Name REGEXP '[[:<:]]Jone[[:>:]]' AND REGEXP '[[:<:]]Deo[[:>:]]';

This would also handle the case where you just wanted to assert that Jone and Deo appear as standalone words anywhere in the name, even if the name had e.g. a middle component.

share|improve this answer

answered Mar 28 at 9:51

Tim BiegeleisenTim Biegeleisen

272k1414 gold badges120120 silver badges182182 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  But how can I split the words ? Is this possible without PHP's explod or loop ?
  
  – Darshak DRC
  Mar 28 at 10:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What do you mean by "split the words?"
  
  – Tim Biegeleisen
  Mar 28 at 10:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In search box, users will enter "John Deo" together. As per you solution need to explode/split the whole word into 2 words.
  
  – Darshak DRC
  Mar 28 at 10:05
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Use PHP's split function? It is up to you how you separate the terms entered. My answer (and your original question) have to do with what query you could run given those two separate terms, not how to obtain them from a PHP script.
  
  – Tim Biegeleisen
  Mar 28 at 10:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Actually, I don't need to use split/explod or any loop of PHP. Is this possible using mysql query?
  
  – Darshak DRC
  Mar 28 at 10:08
  

  
  
  
  

 | 
show 1 more comment

We can use REGEXP here:

SELECT ID, Name
FROM yourTable
WHERE Name REGEXP '[[:<:]]Jone[[:>:]]' AND REGEXP '[[:<:]]Deo[[:>:]]';

This would also handle the case where you just wanted to assert that Jone and Deo appear as standalone words anywhere in the name, even if the name had e.g. a middle component.

share|improve this answer

answered Mar 28 at 9:51

Tim BiegeleisenTim Biegeleisen

272k1414 gold badges120120 silver badges182182 bronze badges

SELECT ID, Name
FROM yourTable
WHERE Name REGEXP '[[:<:]]Jone[[:>:]]' AND REGEXP '[[:<:]]Deo[[:>:]]';

share|improve this answer

answered Mar 28 at 9:51

Tim BiegeleisenTim Biegeleisen

272k1414 gold badges120120 silver badges182182 bronze badges

answered Mar 28 at 9:51

Tim BiegeleisenTim Biegeleisen

272k1414 gold badges120120 silver badges182182 bronze badges

answered Mar 28 at 9:51

Tim BiegeleisenTim Biegeleisen

272k1414 gold badges120120 silver badges182182 bronze badges

0

If you can append % at the beginning and end of each word of the string, you can use the following query:-

SELECT * FROM `table` WHERE 
name 
LIKE 
(SELECT SUBSTRING_INDEX("%Jone% %Deo%", " ", -1)) 
OR 
(SELECT SUBSTRING_INDEX("%Jone% %Deo%", " ", 1))

Tested and working for both Deo Jone and Jone Deo.

share|improve this answer

answered Mar 28 at 10:45

Rakesh JakharRakesh Jakhar

4,89422 gold badges77 silver badges1818 bronze badges

add a comment
 | 

0

If you can append % at the beginning and end of each word of the string, you can use the following query:-

SELECT * FROM `table` WHERE 
name 
LIKE 
(SELECT SUBSTRING_INDEX("%Jone% %Deo%", " ", -1)) 
OR 
(SELECT SUBSTRING_INDEX("%Jone% %Deo%", " ", 1))

Tested and working for both Deo Jone and Jone Deo.

share|improve this answer

answered Mar 28 at 10:45

Rakesh JakharRakesh Jakhar

4,89422 gold badges77 silver badges1818 bronze badges

add a comment
 | 

If you can append % at the beginning and end of each word of the string, you can use the following query:-

SELECT * FROM `table` WHERE 
name 
LIKE 
(SELECT SUBSTRING_INDEX("%Jone% %Deo%", " ", -1)) 
OR 
(SELECT SUBSTRING_INDEX("%Jone% %Deo%", " ", 1))

Tested and working for both Deo Jone and Jone Deo.

share|improve this answer

answered Mar 28 at 10:45

Rakesh JakharRakesh Jakhar

4,89422 gold badges77 silver badges1818 bronze badges

SELECT * FROM `table` WHERE 
name 
LIKE 
(SELECT SUBSTRING_INDEX("%Jone% %Deo%", " ", -1)) 
OR 
(SELECT SUBSTRING_INDEX("%Jone% %Deo%", " ", 1))

share|improve this answer

answered Mar 28 at 10:45

Rakesh JakharRakesh Jakhar

4,89422 gold badges77 silver badges1818 bronze badges

answered Mar 28 at 10:45

Rakesh JakharRakesh Jakhar

4,89422 gold badges77 silver badges1818 bronze badges

answered Mar 28 at 10:45

Rakesh JakharRakesh Jakhar

4,89422 gold badges77 silver badges1818 bronze badges