March 4, 2009 at 7:00 am
Results should be brought up for any match of the query as substring for any of the records in the table. If there are more than one word (tokens) in the search query results that contain all as substrings, regardless of the order, should show.
E.g.
Query: aa bb cc
Results:
aa cc bb
aabbcc
abbaa cc
ccabba aab
etc.
March 4, 2009 at 7:05 am
Please add more explanation about your problem or read the article, link of this article is in my signature!
:w00t:
March 4, 2009 at 7:07 am
your question reads like it came straight from a problem in a textbook...telling us what the required results are...are we doing your homework?
what have you tried so far?
I can tell you that you will need to split the input based on the delimiter(space), and dynamically create a WHERE statement with an AND statement for each of your terms:
AND columnname LIKE '%[each of your terms]%'
show us what you've done so far, and what the table structure you are searching looks like as well.
Lowell
March 5, 2009 at 2:25 am
Well, whatever you're talking, I have done it already before. But the things I need got changed now, If you could try this out you may understand my so called "text-book" problem. Thanks
Following sp, iterates through each column of table and finds possible match in each column of rows. But my requirement is, it should show only those rows which have all of the substrings in their columns, if any substring not found it should not qualify for the result. I know I could do this by little more tricking here but I am looking for any short method to do this.
Note: I have used function to split the substring
create proc [dbo].[sp_SearchObjectContent]
(
@objectName varchar(100),
@SearchString varchar(8000)
)
as
declare @strSelect varchar(8000)
declare @strWhere varchar(8000)
declare @cols varchar(100)
declare @strings varchar(100)
--declare @objectName varchar(100)
--declare @SearchString varchar(8000)
--set @objectName = 'client'
--set @SearchString = 'Kat'
set @strWhere = ''
set @strSelect = ''
declare cur_columns cursor for
select sc.name cols from sysobjects so inner join syscolumns sc
on so.id=sc.id
where so.name=@objectName
open cur_columns
fetch next from cur_columns into @cols
while (@@fetch_status = 0)
begin
declare cur_strings cursor for
select strValue from dbo.StringtoTable(@SearchString)
open cur_strings
fetch next from cur_strings into @strings
while (@@fetch_status = 0)
begin
set @strWhere = @strWhere + @cols + ' like ' + '''%' + @strings + '%''' + ' or '
--print @strWhere
fetch next from cur_strings into @strings
end
close cur_strings
deallocate cur_strings
fetch next from cur_columns into @cols
end
--print @strWhere
set @strSelect = 'select * from ' + @objectName + ' where ' + @strWhere
set @strSelect = left(@strSelect, len(@strSelect) - 3)
--print @strSelect
exec(@strSelect)
close cur_columns
deallocate cur_columns
March 5, 2009 at 6:23 am
sorry if my post bothered you dev, we get a lot of new posters who ask for help on their homework, without putting any effort in themselves.
For your issue, I've done something very similar, which I've modified below.
search all the columns in a table for a specific string...
i've decided that using CHARINDEX for each of the terms is better than using a LIKE statement.
The logic is this: build the queries dynamically and run them, and if any match the needed criteria, returnt eh SELECT statements so you can review the data yourself.
see if this works for you:
[font="Courier New"]
CREATE PROCEDURE TABLEVIEWSEARCH @TABLENAME VARCHAR(60),@SEARCHSTRING VARCHAR(8000)
-- EXEC TABLEVIEWSEARCH 'GMACT','aa,bb,cc'
-- EXEC TABLEVIEWSEARCH 'TABLEORVIEW','TEST'
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(500),
@COLUMNNAME VARCHAR(60)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #TMPCOLLECTION
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.NAME = @TABLENAME
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT COLNAME FROM #TMPCOLLECTION ORDER BY COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
--SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
SET @SQL = 'IF EXISTS(SELECT * FROM [' + @TABLENAME + '] WHERE CHARINDEX('''
+ REPLACE(@SEARCHSTRING,',',''',' + @COLUMNNAME + ') > 0 AND CHARINDEX(''') + ''',' + @COLUMNNAME + ') > 0'
+ ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM ['
+ @TABLENAME + '] WHERE CHARINDEX(''''' + + REPLACE(@SEARCHSTRING,''',''',''''''',''' + @COLUMNNAME + ''') > 0 AND CHARINDEX(''''') + ''''','
+ @COLUMNNAME + ') > 0 '');'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
[/font]
Lowell
March 6, 2009 at 1:37 am
Lowell, thanks for your efforts. Yet, I didn't try the same but i'll surely try out this once I finish my other tasks. For now, this one's low priority issue for the client.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply