SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Timothy A Wiseman / Finding Values With Numerous Columns / Latest Posts

RE: Finding Values With Numerous Columns

Jason Akin — Thu, 04 Dec 2008 12:11:33 GMT

I just noticed something else with my "In" syntax. If you put the IN clause into a JOIN, you can use the LIKE against the original source table for a set based approach instead of concatenating.CREATE TABLE tblAccount ( pkAccountId int, Acctnum varchar(20), fkOverDraftId1 int, fkOverDraftId2 int)SELECT main.Acctnum, qry.AcctnumFROM tblAccount main INNER JOIN tblAccount qry ON qry.pkAccountid IN (main.fkOverDraftId1, main.fkOverDraftId2)WHERE qry.AcctNum LIKE '%123%'

RE: Finding Values With Numerous Columns

Phil Factor — Thu, 04 Dec 2008 10:26:18 GMT

[quote]I just thought people might appreciate another real world example of this kind of problem[/quote]We do, we do. It is strange how quickly one forgets this sort of horror. it is nice to be reminded. Thanks JJ B, thanks timothy.

RE: Finding Values With Numerous Columns

JJ B — Thu, 04 Dec 2008 09:57:15 GMT

I appreciate this article and discussion.I have to do ad-hoc report queries off of a Sybase database created by an other agency (let's call them the State). I have zero input on database design, practically no documentation, and only have read access to the data tables. I cringe every time my users ask for queries that require finding what "program" a client is in. Why? Because the State created a set of 8 columns all containing "program" data. Users can enter 3 characters into each column to indicate the set of programs the client has. A client can have anywhere from zero up to 8 programs. There is no order to the columns and more than 8 possible programs, though the client will never have more than 8 programs at once.What this leaves me with is this: When the users ask, "How many clients have program ABC?" Then I have to search all 8 columns. And of course, it is never as easy as that. They usually have queries like, "How many clients have any of the following programs, ABC, XFG, T3B, ... but not GTH or XYZ."So, why did the State set up the Sybase database that way? Don't they know anything about database design? I'm not privy to any of their conversations or programming problems. So, I don't know. But most of their database (while not a schema I would choose) is fairly workable and mostly normalized (though they did do that annoying phone thing talked about in the article). Why then set up denormalized data for something as vital as a client's program?My thoughts:1) The program data is part of a set of data that has to mirror/be merged with a legacy mainframe database. I have no doubt that the mainframe system is set up with the same 8 columns. 2) But that doesn't explain why they wouldn't use programming to normalize the data in the database I access. All I can think of here is that they weren't thinking about querying the data and they were mostly concerned with saving time on programming the part that merges data between the mainframe and the normal database.I'll tell you, if it was me, I would have done the extra programming so that the data was normalized in the Sybase database. It is a terrible pain working with their data as-is! But as the article points out, when you don't have a choice, it is nice to have techniques to handle the situation. I just thought people might appreciate another real world example of this kind of problem. Cheers.

RE: Finding Values With Numerous Columns

timothyawiseman — Wed, 03 Dec 2008 13:08:47 GMT

[quote][b]Douglas Osborne (12/3/2008)[/b][hr]Tim,Why use LIKE if you are not adding % - shouldn't you just say = instead then?Doug[/quote]For flexibility, I wanted to be able to use a wildcard such as % or _, or not, depending on what I was searching for. Sometimes I had an exact value to search for and sometimes I was looking for a substring.

RE: Finding Values With Numerous Columns

Douglas Osborne-456728 — Wed, 03 Dec 2008 12:18:28 GMT

Tim,Why use LIKE if you are not adding % - shouldn't you just say = instead then?Doug

RE: Finding Values With Numerous Columns

timothyawiseman — Wed, 03 Dec 2008 10:01:28 GMT

[quote][b]Douglas Osborne (12/3/2008)[/b][hr]Hey,This didn't work for me until I added a % at the end of the first dynamic SQL statement:SELECT @sql = CASEWHEN @sql IS NULL THEN 'Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + -- Add below' %'' 'WHEN @sql IS NOT NULL THEN @sql + 'UNION ALL Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + ''' 'Doug[/quote]Adding the % directly in the dynamic sql statement forces it to always look for anything that starts with the value passed in. The way I use it is to put the % in the value passed in, just like using like directly in a select statement. That way I can have it search for the beginning or ending or any combination.

RE: Finding Values With Numerous Columns

Douglas Osborne-456728 — Wed, 03 Dec 2008 08:54:14 GMT

Hey,This didn't work for me until I added a % at the end of the first dynamic SQL statement:SELECT @sql = CASEWHEN @sql IS NULL THEN 'Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + -- Add below' %'' 'WHEN @sql IS NOT NULL THEN @sql + 'UNION ALL Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + ''' 'Doug

RE: Finding Values With Numerous Columns

Timbo-722948 — Tue, 02 Dec 2008 15:22:52 GMT

I enjoyed this article and will definitely use it. I work as a data analyst for an internal audit group, and we often come across data that we know relatively little about. SQL Server is one of the tools we use to handle high volume data, but it is a less-than-perfect audit tool, especially for text searching. Readers may also be interested in the Levenshtein Edit Distance algorithm, which is useful for finding inexact string matches:http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=502&lngWId=5Thanks!

RE: Finding Values With Numerous Columns

bob_chauvin — Tue, 02 Dec 2008 14:54:13 GMT

I see this as useful (with some modification) for deciphering Microsoft SharePoint content databases, which are de-normalized to provide generic functionality.When I create a list in SharePoint and want to get at that list data outside of the SharePoint API I create views, but have figure out what generic column was assigned to hold my data. This is a very tedious task, and if I move the list to another site I have to manually re-map the view.I can in theory use this type of script to find the columns by creating a control row with specific values, and then run this util to determine the column names.

RE: Finding Values With Numerous Columns

ScottPletcher — Tue, 02 Dec 2008 14:40:14 GMT

What about an approach that used a generic stored proc to dynamically create static code for a specific table?That is, the code could handle any table name passed to it, and optionally column(s) to exclude, and would generate code specifically for that table. You execute the resulting code to do the actual search.That way the code is specific and can be tuned but the flexibility is there to handle to any table, albeit at the cost of one extra step per table.

RE: Finding Values With Numerous Columns

timothyawiseman — Tue, 02 Dec 2008 12:29:31 GMT

[quote][b]Madhivanan (12/2/2008)[/b][hr]Just want to point out that ISNUMERIC and ISDATE is not reliableSELECT ISNUMERIC('12d5'),ISDATE('2000')[/quote]You are right that they sometimes return positives on things that might not be intended as numbers or dates, but it seems pretty reliable in identifying what SQL will convert into dates or numbers. For instance, it interprets the d as scientific notation exactly like an e and it takes 2000 as the year and defaults to Jan 1.Still, that may let it return more than intended if not used carefully. Thank you for pointing it out.

RE: Finding Values With Numerous Columns

Charles Kincaid — Tue, 02 Dec 2008 11:57:03 GMT

[quote][b]timothyawiseman (12/2/2008)[/b][hr]... I am not aware of it actually "failing" on a large table, but it can definitely be painfully slow on large tables[/quote]Anything to do with LIKE forcing a table scan? Don't get me wrong. I like this solution as a way around certain problems. I'm looking at adding TEXT and NTEXT column types to your script. I have one of those projects where we store e-mails in a database. I want to do searches where certain words or phrases occur in either the subject, body, or response. They are looking at adding a comment column and your proc would just automatically start looking at that too.

RE: Finding Values With Numerous Columns

antonio.collins — Tue, 02 Dec 2008 11:56:12 GMT

[quote][b]Jason Akin (12/2/2008)[/b][hr]This doesn't work for a LIKE condition, but for equalities in a small number of fields, you can use [code]WHERE '411-555-1212' IN (HomePhone, WorkPhone, CellPhone, AltPhone)[/code]I've found it useful when looking for a foreign key of a Person Id in several fields like fkLoanOfficerId, fkApprovingOfficerId, fkVerifiedBy etc.You can't let the perfect be the enemy of the good. In some cases it's more expedient to work with the errors of the past, than to attempt a total rewrite of the structure.[/quote]+1. The IN list of columns is far better than a bunch of OR conditions. Likewise, if LIKEs are necessary this would be a better alternative:[code]WHERE (HomePhone+'|'+WorkPhone+'|'+CellPhone+'|'+AltPhone) like '%555-1212%' [/code]

RE: Finding Values With Numerous Columns

timothyawiseman — Tue, 02 Dec 2008 11:11:50 GMT

[quote][b]Jeff Moden (12/2/2008)[/b][hr]... or a nice article on SSC, Phil. ;)[/quote]I'll hop on the bandwagon and say I would love to see this article, Phil.And you are quite correct. I originally wrote the procedure to help me deal with large numbers of "spreadsheet-like" tables with swaths of repeating columns. I am not aware of it actually "failing" on a large table, but it can definitely be painfully slow on large tables

RE: Finding Values With Numerous Columns

Jerry Hung — Tue, 02 Dec 2008 09:39:47 GMT

I guess I like the code, so I tweaked it a bit into a SELECT only, and added 'column' filtersso you can only search in SELECTED columns (if you know the names ahead)It's funny we are trying to simulate Full-Text searchAdventureWorks2008 DB[code]SET @schema = 'Person' SET @TableName = 'Person'SET @Value = 'Xu%'SET @ColumnNames = 'FirstName,LastName' -- can be empty or * for ALL columns[/code][code]/* CREATE PROCEDURE [dbo].[FindValue] @TableName NVARCHAR(128), /* Must be a valid table or view name, must not be quoted or contain a schema*/ @Value NVARCHAR(4000), /*May contain wildcards*/ @schema NVARCHAR(128) = 'dbo' /*May be left out*/ AS Sample Execution Exec FindValue @TableName = 'spt_monitor', @Value = '8', @schema = 'dbo' */ /* If given a string it will finds all rows where any char, varchar, or their Unicode equivalent which contain that string in the selected table or view. Note that this only works on objects which have entries in information_schema.columns, which excludes certain system objects. If given a numeric value it will check those text types for a match as well as numeric types. If given a possible date, it will also check date type. The string that is being searched for may contain wildcard characters such as %. This will NOT search text, ntext, xml, or user defined fields. This may return a row more than once if the search string is found in more than one column in that row. */ DECLARE @TableName NVARCHAR(128), /* Must be a valid table or view name, must not be quoted or contain a schema*/ @Value NVARCHAR(4000), /*May contain wildcards*/ @schema NVARCHAR(128) /*May be left out*/ ,@ColumnNames NVARCHAR(4000) -- list of columns to search for, can be * for ALLSET @schema = 'Person' SET @TableName = 'Person'SET @Value = 'Xu%'SET @ColumnNames = 'FirstName,LastName' -- can be empty or * for ALL columnsSET @ColumnNames = REPLACE(@ColumnNames, ' ', '') -- removes all space /**************************** Declare Variables ***********************/ DECLARE @columns TABLE (ColumnName NVARCHAR(128)) DECLARE @columnsFiltered TABLE (ColumnName NVARCHAR(128)) DECLARE @sql NVARCHAR(MAX) /************************** Populate Table Variable *****************/ /*Takes the names of string type columns for the selected table */ INSERT INTO @columns (ColumnName) SELECT Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_schema = @schema AND Table_name = @TableName AND data_type IN ('char', 'nchar', 'varchar', 'nvarchar') /* If it is numeric, also check the numeric fields */ IF ISNUMERIC(@value) = 1 INSERT INTO @columns (ColumnName) SELECT Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_schema = @schema AND Table_name = @TableName AND data_type IN ('int', 'numeric', 'bigint', 'money', 'smallint', 'smallmoney', 'tinyint', 'float', 'decimal', 'real') IF ISDATE(@value) = 1 INSERT INTO @columns (ColumnName) SELECT Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_schema = @schema AND Table_name = @TableName AND data_type IN ('datetime', 'smalldatetime') INSERT INTO @columnsFilteredSELECT ColumnNameFROM @columnsWHERE (@ColumnNames IN ('*','') OR CHARINDEX(',' + ColumnName + ',', ',' + @ColumnNames + ',') > 0 )/********************* Prepare dynamic SQL Statement to Execute **********/ SELECT @sql = CASE WHEN @sql IS NULL THEN 'Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + ''' ' WHEN @sql IS NOT NULL THEN @sql + 'UNION ALL Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + ''' ' END FROM @columnsFiltered /******************* Execute Statement and display results ***********/ --print @sql /* This may be uncommented for testing purposes */ EXEC (@sql) [/code]

RE: Finding Values With Numerous Columns

Charles Kincaid — Tue, 02 Dec 2008 09:11:11 GMT

Madhivanan and Jason bring up some good points. Formating! Then if you like LIKE you'll [b][i]love[/i][/b] this. Try a search argument with an embedded RegEx. Search for '*e*' and stand by for a boat load of rows. I'm pushing to store phone numbers as BigInt. I don't have to [b][i][u]dial[/u][/i][/b] the dashes why should I have to store them? Oh, and three fields too. Country code, area code, phone number. OK, I know that there [u]are[/u] letter on the phone but the phone [b][i]system[/i][/b] could care less.I have customers that use ISO 8601 dates (20081202 for today). Goes in a char(8). On one hand no messy times. :) A ship date is a ship date. On the other try doing calculations by week. :( I plan on finding a real good reason for these folks to migrate from 2000 to 2008. The date type has me all a twitter.

RE: Finding Values With Numerous Columns

Anipaul — Tue, 02 Dec 2008 08:17:15 GMT

[quote][b]Phil Factor (12/2/2008)[/b][hr][quote]... or a nice article on SSC, Phil. [Wink]Speaking for the vast silent majority, I'd like to read Phil's article. [BigGrin][/quote]OK. It's a deal. I needed a good excuse to publish it! :)[/quote]Phil is it comming in Jan '09....

RE: Finding Values With Numerous Columns

Jason Akin — Tue, 02 Dec 2008 07:55:12 GMT

This doesn't work for a LIKE condition, but for equalities in a small number of fields, you can use [code]WHERE '411-555-1212' IN (HomePhone, WorkPhone, CellPhone, AltPhone)[/code]I've found it useful when looking for a foreign key of a Person Id in several fields like fkLoanOfficerId, fkApprovingOfficerId, fkVerifiedBy etc.You can't let the perfect be the enemy of the good. In some cases it's more expedient to work with the errors of the past, than to attempt a total rewrite of the structure.

RE: Finding Values With Numerous Columns

Madhivanan-208264 — Tue, 02 Dec 2008 07:11:49 GMT

Just want to point out that ISNUMERIC and ISDATE is not reliableSELECT ISNUMERIC('12d5'),ISDATE('2000')

RE: Finding Values With Numerous Columns

Phil Factor — Tue, 02 Dec 2008 06:41:45 GMT

[quote]... or a nice article on SSC, Phil. [Wink]Speaking for the vast silent majority, I'd like to read Phil's article. [BigGrin][/quote]OK. It's a deal. I needed a good excuse to publish it! :)

RE: Finding Values With Numerous Columns

Paul DB — Tue, 02 Dec 2008 06:31:21 GMT

[quote][b]Jeff Moden (12/2/2008)[/b][hr]... or a nice article on SSC, Phil. ;)[/quote]Speaking for the vast silent majority, I'd like to read Phil's article. :D

RE: Finding Values With Numerous Columns

tskelley — Tue, 02 Dec 2008 06:11:08 GMT

Thanks for the article as this does come up, especially when dealing with others' legacy databases. I would be interested in seeing this expanded to include text fields, as so often a varchar is used to capture field data, then it overspills into a catch-all notes field.

RE: Finding Values With Numerous Columns

Jeff Moden — Tue, 02 Dec 2008 05:24:29 GMT

... or a nice article on SSC, Phil. ;)

RE: Finding Values With Numerous Columns

Phil Factor — Tue, 02 Dec 2008 02:54:30 GMT

This is an interesting article. What is most important is that it explains WHY there can be a problem, for anyone who has never been in a position of trying unsuccessfully to curb developers who possess just a little knowledge of Database Design but who are completely unaware of just how little.As far as I can see, the solution that Timothy shows will work well in cases where the database has a lot of small spreadsheet-like tables, (Timothy seems to have suffered from this) but it will not work for monster tables unless it has some very clever indexing.There is a different solution that works very well, which I've had to use myself many times, though it is not a good idea for a rapidly changing table. It is, however, very fast where there is lots of text to search in several columns, which would otherwise need a '%xxxx%' wWHERE-clause. (i.e. unindexable!) This is to use an 'inversion' table. this technique is usually called the ‘Inverted’ or ‘Inversion’ index technique. (see[url] http://en.wikipedia.org/wiki/Index_(search_engine)[/url] for a full discussion.Basically, you produce table that contains, uniquely, a list of every word in the columns you want to index. You maintain a many-to-many linking table that links the row in the denormalized table with the words in the unique table that it contains. This gives you, instantly, the rows containing the words in the 'search string' that you want to search for, even in tables that are several million rows long. You can refine your search from that subset, and Timothy's method should work fine for that. I've never tried to make this into a generic solution, as I'm not that brave, nor unfortunate enough to have more than one travesty of a denormalized table like the ones Timothy describes, within a single database! If anyone is interested, I'll pop the solution into a blog post, but it is too long for a forum post, I reckon..

RE: Finding Values With Numerous Columns

nvanesch — Tue, 02 Dec 2008 02:25:37 GMT

If you are happy to exclude index performance benefits or doing a like comparison you can create a computed column of the concatenation of other columns and then search the computed column.[code]ALTER TABLE phonebook ADD phoneall AS ',' + isnull(phone1, '') + ',' + isnull(phone2, '') + ',' + isnull(workphone, '') + ',' + isnull(cellphone, '') + ','[/code][code]SELECT *FROM phonebookWHERE phoneall like '%,234-5678,%'[/code]

RE: Finding Values With Numerous Columns

Anipaul — Tue, 02 Dec 2008 02:13:41 GMT

Good one ...

Finding Values With Numerous Columns

timothyawiseman — Mon, 01 Dec 2008 23:07:59 GMT

Comments posted to this topic are about the item [B]Finding Values With Numerous Columns[/B]