Hello All, Can anyone help me on this Please. I have a table with 53 columns. Cola,colb,colc, col1,col2,col3….col 50 I need to look for a value say X in either col1 or col2 or col3…. Col 50 And need to display the result Result must contain column like Cola,colb,colc and only that column from col1 –col 50 which contain the value X Thank You Very Much for your support.
How about making use of a full text index (and a little hacking)? Step 1: Concatenate your column values to a single column in the table. Step 2: Create a full text index (FTI) on this column. Step 3: Use a search term and the FTI to return a set of keys for the rows that contain the term. Step 4: Pass the keys to an UNPIVOT query to isolate the matches. You should probably have a think about what to do with rows with multiple matches. Example code below. Note that I'm working on a server without FTI installed, so didn't physically test the FTI bits, but they're probably there or thereabouts. Links at bottom might be of interest. ----------------- -- create a table ----------------- create table dbo.MyTable ( MyId int identity , Col1 varchar(20) , Col2 varchar(20) , Col3 varchar(20) , Col4 varchar(20) , Col5 varchar(20) , Col6 varchar(20) , MyConcat varchar(250) ) go alter table dbo.MyTable add constraint pk_MyTable primary key clustered (MyId) go -- add some data insert dbo.MyTable (Col1, Col2, Col3, Col4, Col5, Col6) values('Mary','Had','A','Little','Lamb',null) insert dbo.MyTable (Col1, Col2, Col3, Col4, Col5, Col6) values('It''s','Fleece','Was','White','As','Snow') insert dbo.MyTable (Col1, Col2, Col3, Col4, Col5, Col6) values('And','Everywhere','That','Mary','Went',null) insert dbo.MyTable (Col1, Col2, Col3, Col4, Col5, Col6) values('That','Lamb','Was','Sure','To','Follow') --------------------------- -- concatenate data columns --------------------------- -- null values might spoil the party set concat_null_yields_null off update dbo.MyTable set MyConcat = Col1 + ' ' + Col2 + ' ' + Col3 + ' ' + Col4 + ' ' + Col5 + ' ' + Col6 -- show data select * from dbo.MyTable ------------- -- create FTI ------------- -- note - i'm working on a server without FTI installed, so we'll just have to spoof the results later -- we need a catalog first create fulltext catalog ft as default; go -- create the index create fulltext index on dbo.MyTable(MyConcat) key index pk_MyTable go ------------ -- query FTI ------------ -- pass the keys returned to a temp table to use later create table #MyKeys (MyId int) -- always use nvarchar types with FTI for performance reasons declare @MySearchTerm nvarchar(20) set @MySearchTerm = 'Mary' insert #MyKeys select MyId from dbo.MyTable where contains(MyConcat,@MySearchTerm) -- this is the bit where i fake a resultset... insert #MyKeys values (1) insert #MyKeys values (3) -- index up create clustered index ix_tmp on #MyKeys(MyId) --------------------------- -- return items of interest --------------------------- declare @MySearchTerm nvarchar(20) set @MySearchTerm = 'Mary' select * from ( select mytb.MyId, Col1, Col2, Col3, Col4, Col5, Col6 from dbo.MyTable mytb join #MyKeys myks on mytb.MyId = myks.MyId ) src unpivot ( TermToSearch for TermFoundIn in ( Col1, Col2, Col3, Col4, Col5, Col6 ) ) unpvt where TermToSearch = @MySearchTerm
http://msdn.microsoft.com/en-us/library/ms142571.aspxhttp://msdn.microsoft.com/en-us/library/ms142583.aspx Edit: Carriage returns and stuff...
That's a pretty frightening database design. There's not going to be an easy way to only include the column that matches in the result set unless all the other columns are NULL. Then you could use a COALESCE to ensure the first non-null column is what gets returned. As it is, with the structure as you have it, I think you'll have to have two things. First, a very long winded set of OR statements in the WHERE clause, WHERE Col1 = @MyVal OR Col2 = @MyVal and then you'll have to build a CASE statement for all 50 columns in the SELECT list so that you check for the value of Col1, etc, to return the first matching column. It's going to be a very long and ugly query. It's going to perform extremely poorly. There's really nothing you can do to change those two facts with the existing non-normalized structure.
Hi, Few weeks ago i was prepairing a [script] for a global database search, it looks for a value in every available column for each table, you can modify it and get the result you need. in return you get the Primary Key of the row with the value, what is the primary key column name, table nam where the value was found and the value column name. : /storage/temp/1570-globalsearchforstringbycolumns.txt