SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is the command for search in a database.

    Please someone tell me the command for searching in whole table of a database,
    like If I want to search whole table (not by any single field or column)

    For example, I want something similar command to this
    Code MySQL:
    SELECT * FROM `school_table` like '%mohan%'

    Please note, I know the other command for searching but that is only for searching in any particular row, for example.
    Code MySQL:
    SELECT * FROM `school_table` where name like '%mohan%'

  2. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you give an example of what you want returned from the search?

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes sure, For example, I want to return all the columns (e.g. ID, Name etc.) of a table, where any field name is consisting of my name.
    My name is mohan.

    So, if a table consists of mohan anywhere in it, it should return me that column.

    Thanks.

  4. #4
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, say your table has 3 columns in it - col1, col2 and col3

    Code:
     
    select * from tblName 
    where col1 like '%mohan%' or col2 like '%mohan%' or col3 like '%mohan%';
    will return all the columns where mohan is in any of them.

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is the command of searching this thing in a table of mysql?

    What is the command of searching this thing in a table of mysql?

    For example, I have a record mohan in one of my mysql-table, and when i search the database by the name mohan, it returns the rows wherever the column has name mohan in it.
    Code MySQL:
    select * from names where name like '%mohan%'
    This is very simple.

    But I felt said when I searched by the name moh an, there was no rows returned.
    and the same thing happened when i searched moh-an.

    something like
    Code MySQL:
    select * from names where name like '%mo han%'

    or

    Code MySQL:
    select * from names where name like '%mo-han%'


    Is there any way to search all the characters in that column name and figure out what i want?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mohansinfh View Post
    Is there any way to search all the characters in that column name and figure out what i want?
    not based on the example you gave, no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mohansinfh View Post
    ... I want to return all the columns (e.g. ID, Name etc.) of a table, where any field name is consisting of my name.
    well, for starters, you wouldn't need to look into any column that is numeric, like the id column

    so you wouldn't search all the columns, just the string ones

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to all of you.
    I got the way.
    Thanks again.

  9. #9
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh sir, some other method to get some similar results ?????

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you could do this --
    Code:
    WHERE name LIKE CONCAT('%',REPLACE(REPLACE('mo han',' ',''),'-',''),'%')
    but you should probably remove the spaces and dashes in your application language (php?) before searching
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you sir.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •