SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding Values With Numerous Columns


Finding Values With Numerous Columns

Author
Message
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9827 Visits: 1407
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! Smile


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



Charles Kincaid
Charles Kincaid
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3305 Visits: 2384
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. Smile A ship date is a ship date. On the other try doing calculations by week. Sad 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.

ATBCharles Kincaid
Jerry Hung
Jerry Hung
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3492 Visits: 1208
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
timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2394 Visits: 920
Jeff Moden (12/2/2008)
... or a nice article on SSC, Phil. Wink


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/
antonio.collins
antonio.collins
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2238 Visits: 921
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%' 


Charles Kincaid
Charles Kincaid
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3305 Visits: 2384
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.

ATBCharles Kincaid
timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2394 Visits: 920
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/
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19839 Visits: 7413
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
bob_chauvin
bob_chauvin
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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.
Timbo-722948
Timbo-722948
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search