question

AlexKlap avatar image
AlexKlap asked

Looking for a value in multiple column

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.
sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
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.aspx http://msdn.microsoft.com/en-us/library/ms142583.aspx Edit: Carriage returns and stuff...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AlexKlap avatar image AlexKlap commented ·
Thank You Robertson, this is a master piece of code. i will mark it as answer once implement it in my scenerio. Thanks Again.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AlexKlap avatar image AlexKlap commented ·
Hi Grant,Table is like this. Code,Name,Des_1,Des_1_Value,Des_2,Des_2_Value,Des_3, Des3_Value AAA,Auto,Type,Universal,Pieces,DISC,Design, No Logo ALS,Sales,Heater,Knob,Type, Heater, Design, DISC BII,Trim, Kit,DISC,Type,Kit,Finish, Coated Looking for output like this: Code Name Des Desval AAA Auto Pieces DISC ALS Sales Design DISC BII Trim Kit DISC
0 Likes 0 ·
KenJ avatar image KenJ commented ·
@AlexKlap - what query do you have so far?
0 Likes 0 ·
AlexKlap avatar image AlexKlap commented ·
Hey Grant. your logic is perfectly working fine, but we cannot mark 2 suggestion as answer. any suggestion how to do it in sql central. Have a great day.
0 Likes 0 ·
emil87b avatar image
emil87b answered
Hi, Few weeks ago i was prepairing a [script][1] 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. [1]: /storage/temp/1570-globalsearchforstringbycolumns.txt

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AlexKlap avatar image AlexKlap commented ·
Thanks Emil, This is really great script. is it possible to have two table and a column from two different table. i mean can we check data from two different table. for eg:- ID,PrimaryKey,PrimaryColumn ,TableName,ColumnName,TableName2,ColumnName2 1,ALS945,slno,Compact,description_value2,?,? (here ? for table2 and columnname 2) can we get tablename2 with columnname2. Thanks a ton for your valuable help.
0 Likes 0 ·
AlexKlap avatar image AlexKlap commented ·
Thank a ton guys......
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.