SQL Server: Search input on multiple columns

Question

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.

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
('Griff','Serjey','Maciej',GETDATE()),
('King','Fisher','Ajay',GETDATE()),
('Paul','Griff','Serjey',GETDATE()),
('King','Fisher','Griff',GETDATE())

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.

coulmn4 is datetime. Datetime is incompatible with string. You use it in IN clause. — Paweł Dyl
– Paweł Dyl, Commented Aug 29, 2016 at 9:24

A_Sk · Accepted Answer · 2016-08-29 11:02:20Z

1

to fix your requirement, you can first convert column4, yourdatetime column to nvarchar(max) first

select * from tbl_Test1 where 'Griff' IN
 (column1,coulmn2,coulmn3,convert(nvarchar(max),coulmn4))

however this is not recommended to do so because of performance issues =x

edited Aug 29, 2016 at 11:02

A_Sk

4,6505 gold badges31 silver badges55 bronze badges

answered Aug 29, 2016 at 9:27

roy

653 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheGameiswar · Accepted Answer · 2016-08-29 09:25:58Z

0

From 2012,,you can use..TRY_Convert

select * from #tbl_Test1 
where 'Griff'
 IN (column1,coulmn2,coulmn3,try_convert(varchar(10),coulmn4))

You may need to do similar cast and convert on other columns when you search with date columns

answered Aug 29, 2016 at 9:25

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

3 Comments

Monojit Sarkar Over a year ago

getting error when execute your code the error is 'varchar' is not a recognized built-in function name.

Monojit Sarkar Over a year ago

if i use convert instead of try_convert then all goes fine but try_convert throwing error 'varchar' is not a recognized built-in function name. i am using SQL Server 2012.

TheGameiswar Over a year ago

may be you are using try_convert like this..try_convert (columnname,varchar)

StackUser · Accepted Answer · 2016-08-29 09:50:42Z

0

You already know that your column4 is of type datetime then why do we search your string with that column. Just ignore that column

Try like this,

SELECT *
FROM tbl_test1
WHERE 'Griff' IN (
        column1
        ,coulmn2
        ,coulmn3
        )

answered Aug 29, 2016 at 9:50

StackUser

5,3983 gold badges29 silver badges48 bronze badges

Collectives™ on Stack Overflow

SQL Server: Search input on multiple columns

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related