0

I am working on an application in asp.net. There is a page to search employee details. User can enter any value to search in multiple fields like 

FirstName, LastName, DateOfBirth, EmployeeCode

User can enter value like this:

 Raj*,1980

That means user want to search Raj* and 1980 among the number of fields given above.

I was looking at full-text search in SQL Server but the problem is that full-text search does not work for DateTime columns.

The data can be coming from multiple tables.

Please give me any solution.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
M.Sharma
  • 269
  • 6
  • 18

2 Answers2

1

Not sure, but it may help you.

Declare @Input as varchar(50) = 'Field1,Field2,Field3'
DEClare @Character as CHAR(1) = ','
DECLARE @StartIndex INT, @EndIndex INT

DECLARE @Output TABLE (ID int IDENTITY(1,1),
Item NVARCHAR(1000)
)

SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END

WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)

INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END

--Finally Received all Search Keyword Values in @Output table
--Now Moving towards Search functionality

DECLARE @Result Table(data varchar(50),ID int)

WHILE (1=1)
BEGIN
IF(SELECT COUNT(1) FROM @Output) > 0
BEGIN
DECLARE @ID_OT INT = 0,@keyword AS VARCHAR(50) = ''
SELECT TOP 1 @ID_OT = ID,@keyword = Item FROM @Output ORDER BY id 

--Here you need to identify a unique column which can be used to filter redundant values
INSERT INTO @Result
SELECT data,id
FROM TableName T
Left join @Result R ON T.id = R.id
WHERE 
((FirstName like '%' + @keyword + '%') OR
(LastName like '%' + @keyword + '%') OR
(EmployeeCode like '%' + @keyword + '%') OR
(ISDATE(@keyword) = 1 AND CONVERT(VARCHAR(15),DateOfBirth,110) like '%' + CONVERT(VARCHAR(15),@keyword,110) + '%'))
AND R.id IS NULL

DELETE FROM @Output WHERE id = @ID_OT

END
ELSE
BREAk
END
Tirthak Shah
  • 515
  • 2
  • 11
0

Why can't you make a "mixed query" like this:

SELECT * FROM MyTable
  WHERE CONTAINS(columnName, '"Toy Dog" OR "live animal"')
    AND start_date > ###;

See also:

Community
  • 1
  • 1
paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • Paulsm4, This looks good. However, how can I handle situation where i need to filter date field with multiple search text. For example Raj*,1980,Male ...there can be multiple comma separated values that I need to search into date filed. In case of Full text search this is easy. – M.Sharma Jul 20 '15 at 08:20
  • "Comma separated values" sounds like "structured data", doesn't it? Q: Why aren't you parsing your csv data and storing it into ordinary columns in SQL tables? – paulsm4 Jul 20 '15 at 18:21
  • Mixed Query is a good option but When used with CONTAINSTABLE , this did not give correct result. – M.Sharma Jul 21 '15 at 06:54