SQL Server: Search input on multiple columns RRS feed

  • Question

  • User264732274 posted

    i was trying to search a single term on multiple columns. i got error because one of column was date time. how could i make a generic search with in-line sql. this way i tried.

    i got the idea from here http://www.codeproject.com/Tips/993715/To-Search-Data-in-Multiple-Columns-using-IN-Clause

    create table tbl_test1
    (id int identity(1,1),
    column1 nvarchar(50),
    coulmn2 nvarchar(50),
    coulmn3 nvarchar(50),
    coulmn4 datetime)-- Create a table 
    insert into tbl_Test1 (column1,coulmn2,coulmn3,coulmn4) values
    select * from tbl_Test1 where 'Griff' IN (column1,coulmn2,coulmn3,coulmn4)

    i got this error.

    Conversion failed when converting date and/or time from character string.

    Monday, August 29, 2016 9:23 AM


  • User753101303 posted


    If you want to include other columns you would have to use https://msdn.microsoft.com/en-us/library/ms187928.aspx (CAST or CONVERT) for those columns. Note that as usual when you convert a datetime to a text, its depends on which country convention you are using.

    My personal preference would be likely to restrict text search to text columns and provide an advanced search for other columns (for example with a date it's quite common to search within a from/to range rather than just for a particular date).

    Edit. So strickly speaking select * from tbl_Test1 where 'Griff' IN (column1,coulmn2,coulmn3,CAST(coulmn4 AS NVARCHAR)) would "work".

    Now the issue is which value the user should type to find a row based on the coulmn4 value. Try :

    SELECT CAST(coulmn4 AS NVARCHAR),CONVERT(NVARCHAR,coulmn4,110),CONVERT(NVARCHAR,coulmn4,103) FROM tbl_test1

    to select the expression you would prefer for your users...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 29, 2016 11:30 AM