Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Finding Values With Numerous Columns Expand / Collapse
Author
Message
Posted Tuesday, December 2, 2008 8:17 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,308, Visits: 1,378
Phil Factor (12/2/2008)
... or a nice article on SSC, Phil. [Wink]

Speaking for the vast silent majority, I'd like to read Phil's article. [BigGrin]


OK. It's a deal. I needed a good excuse to publish it! :)


Phil is it comming in Jan '09....



Post #612098
Posted Tuesday, December 2, 2008 9:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Madhivanan and Jason bring up some good points. Formating! Then if you like LIKE you'll love 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 dial 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 are letter on the phone but the phone system 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.


ATB

Charles Kincaid

Post #612138
Posted Tuesday, December 2, 2008 9:39 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:36 AM
Points: 772, Visits: 1,183
I guess I like the code, so I tweaked it a bit into a SELECT only, and added 'column' filters
so you can only search in SELECTED columns (if you know the names ahead)

It's funny we are trying to simulate Full-Text search

AdventureWorks2008 DB
SET @schema = 'Person'   
SET @TableName = 'Person'
SET @Value = 'Xu%'
SET @ColumnNames = 'FirstName,LastName' -- can be empty or * for ALL columns


/* 
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 ALL

SET @schema = 'Person'
SET @TableName = 'Person'
SET @Value = 'Xu%'
SET @ColumnNames = 'FirstName,LastName' -- can be empty or * for ALL columns
SET @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 @columnsFiltered
SELECT ColumnName
FROM @columns
WHERE
(@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)



SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #612165
Posted Tuesday, December 2, 2008 11:11 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
Jeff Moden (12/2/2008)
... or a nice article on SSC, Phil. ;)


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


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #612273
Posted Tuesday, December 2, 2008 11:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 18, 2014 1:58 PM
Points: 438, Visits: 902
Jason Akin (12/2/2008)
This doesn't work for a LIKE condition, but for equalities in a small number of fields, you can use

WHERE '411-555-1212' IN (HomePhone, WorkPhone, CellPhone, AltPhone)

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.


+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:

WHERE (HomePhone+'|'+WorkPhone+'|'+CellPhone+'|'+AltPhone) like '%555-1212%' 

Post #612308
Posted Tuesday, December 2, 2008 11:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
timothyawiseman (12/2/2008)
... I am not aware of it actually "failing" on a large table, but it can definitely be painfully slow on large tables


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.


ATB

Charles Kincaid

Post #612309
Posted Tuesday, December 2, 2008 12:29 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
Madhivanan (12/2/2008)
Just want to point out that ISNUMERIC and ISDATE is not reliable

SELECT ISNUMERIC('12d5'),ISDATE('2000')


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.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #612329
Posted Tuesday, December 2, 2008 2:40 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:31 PM
Points: 1,977, Visits: 2,926
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.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #612406
Posted Tuesday, December 2, 2008 2:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 7:52 AM
Points: 5, Visits: 43
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.

Post #612418
Posted Tuesday, December 2, 2008 3:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 22, 2010 9:56 PM
Points: 1, Visits: 7
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=5

Thanks!

Post #612443
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse