Results 1 to 6 of 6

Thread: sqlite search all columns

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Aug 2020
    Posts
    29

    sqlite search all columns

    Hi all,

    Im building a small app that has an sqlite Db attached so i need to create a user search function but I don't want the user to have to state which column the string they are looking for is in.
    In a previous project with a CSV file i just added a final column that had all the other columns text concacted together this worked like a charm it also meant the user could search as they types each letter.

    so what is the prefered way of achiving this?

    current i can search a single column with this

    Code:
    conn.Open()
    
                        Dim sql = "SELECT * FROM maintable Where Id=62382387"
    
                        Dim cmdDataGrid As SQLiteCommand = New SQLiteCommand(sql, conn)
    
                        Dim da As New SQLiteDataAdapter
                        da.SelectCommand = cmdDataGrid
                        Dim dt As New DataTable
                        da.Fill(dt)
                        DataGridView1.DataSource = dt
    
                        Dim readerDataGrid As SQLiteDataReader = cmdDataGrid.ExecuteReader()
    kind regards tim

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: sqlite search all columns

    For future reference, don't post code snippets with huge wads of leading whitespace on every line but the first. It always amazes me how many people put no thought into this. If you select a section of code in VS and you start at the first useful character on the first line and drag to the last character on the last line then you're removing the leading whitespace from the first line but not the rest and you end up with what you've got here. You're just making your code harder for us to read for no good reason. At the very least, keep the leading whitespace on the first line too, for consistency. Better yet, think about those reading the code and remove all the whitespace. You could do that manually after pasting the code - it might take a minute to do so is very little effort, given you'd like us to spend far more time than that helping you - but it's far easier than that. If you depress the Alt key while you click and drag in VS then you can select an arbitrary box of code, which allows you to select the code but not the leading whitespace. When you paste, it is now as easy as possible for us to read your code.

    This might seem over the top for a little thing but I spend a lot of time here and on other forums so I read a lot of other people's code and I see this sort of thing all the time and it's just people not bothering to consider how they can help those whom they would like to help them. This post was the straw that broke the camel's this time, so you get to bear the brunt of the frustration that has built up since the last time the dam burst. It may seem like a small thing to you because you only posted one question but it's a big deal for those of us who read and answer lots of questions. It would be nice if the people we help would consider what would help us.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: sqlite search all columns

    As for the question, if you know that you want to compare every column then the proper way to do it is to write SQL code that compares every column. It sounds like you're hoping for something like a wildcard, e.g.
    Code:
    SELECT * FROM MyTABLE WHERE * = value
    SQL doesn't work that way I'm afraid. There is a school of thought that you shouldn't even use wildcards in SELECT clauses. Basically, you know what columns are in your table so you should write SQL code that compares each of those columns in the WHERE clause, e.g.
    Code:
    SELECT * FROM MyTABLE WHERE Column1 = value OR Column2 = value OR Column3 = value
    If your columns are different data types then you have to allow for that too, e.g. you can't compare a numerical column to a non-numeric value. Note that you would actually hard-code the value(s) into the SQL code. You should use parameters and any conversion of data types should happen there.

  4. #4
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: sqlite search all columns

    Taken in consideration that this is SQL Lite but I don't see any reason why it wouldn't work as this is basic SQL, if (although I would have to agree with JMC because you will run in some column data issues sooner or later) you want to simulate your "all in one" CSV concatenation then you can do this:

    Code:
    select t.mergesql from (select  CONVERT(nvarchar, id) + ',' + anothercolumn as mergesql from MyTable) t
    where t.mergesql like '%AvalueIwant%'
    
    or this:
      
    select t.mergesql from (select  CONVERT(nvarchar, id) + anothercolumn as mergesql from MyTable) t
    where t.mergesql like '%AvalueIwant%'
    The first will put a "," so you can do a split or have a reference start or what you want, the second is full concatenation.
    Also note the conversion to an integer (id) to nvarchar , you need to do these stuff if you are merging different data types.
    Also note the draw down that searching with a like, as shown, will make the query non sargable so it will impact performance.

    So as you can see the first suggestion by JMC looks more robust but it's you choice.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: sqlite search all columns

    The potential issue with concatenating fields is that you may end up being able to match a value that doesn't actually exist in any column. It may be possible based on the data and, if it is, you can reduce the likelihood by using a funky delimiter. For instance, If you had "First" in one column and "Second" in another and concatenated them, you'd end up with "First,Second" (assuming you used commas as delimiters) and the user could then match that row by searching for "t,s".

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Aug 2020
    Posts
    29

    Re: sqlite search all columns

    Hi all,
    Thanks for taking the time to read and reply to my question I will try and format questions better in the future.

    I really like the look of jmcilhinney of being able to check within specified columns the ones I’m interested in are
    all the same data type so I’ll give it a go

    Many thanks tim

Posting Permissions

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



Click Here to Expand Forum to Full Width