SQLTeam.com | Weblogs | Forums

Is it possible to search for a '%value%' anywhere in a table?

Hello

I want to search for a '%value%' anywhere in a table after converting the whole table into text.

Is this possible? I so some solutions online but they are very verbose for me to remember them.

Thanks!

Your question doesn't make any sense...

Converting the whole table into text? What does that mean - and how was that done?

And no - you would have to specify every character column in the where clause using LIKE - no reason to include non-character columns.

1 Like

Let's say you extract a whole table to csv and load it to Excel and click 'Find All' and start typing something.
Excel will list all the instances of something regardless their position in the table.

Hope this makes sense? Is it possible?

Thanks

Sure - you can export the data to Excel and search that way. How that applies to SQL Server - not sure...

SQL Server isn't an application like Excel, it is a data repository that stores data in tables. You use queries to get the data - and there isn't anything in SQL (the language) to query all columns for something.

SSMS is an application (like Excel) - so you could run a query with the results pane set to text and then do a find in the results pane. Or you could use JSON or XML as the output and search that...

Probably other ways...

It depends on what you are using it for.

I use a technique I call the "Unfortunate Persons Search Capability" that is NOT designed for lots of rows. It has so many flaws - anyone worth their salt will be able to poke holes in it. But, as my mom used to say about me, "It works, just use it."

It uses the DelimitedSplit8K procedure.

The example I gave has data from the AdventureWorks2012 sample database, specifically the Production.Product table. I created the temp table to hold data but you would have your own real tables.

The benefit from this is you can search on multiple strings each delimited by a space. It's NOT boolean meaning you can't do "Value > 1" or anything like that. Again, this is simple basic stuff that can be tailored to whatever you need.

I didn't include the additional dynamic sorting, paging stuff but that is also something you can do.

I have used this convention for nearly 15 years to solve simple problems and it's only a tool. Just remember to use it for the proper things.

DECLARE @SearchStringFinal	VARCHAR(MAX);
DECLARE @SQLStatement		VARCHAR(MAX);


/* Sample table, replace with your data */
IF OBJECT_ID('tempdb..#ProductTable') IS NOT NULL DROP TABLE #ProductTable;

CREATE TABLE #ProductTable
(
	ProductID				INT IDENTITY(1,1)	NOT NULL PRIMARY KEY,
	ProductName				VARCHAR(50)			NOT NULL,
	ProductNumber			VARCHAR(25)			NOT NULL,
	Color					VARCHAR(15)			NULL,
	StandardCost			MONEY				NOT NULL,
	ListPrice				MONEY				NOT NULL,
	Size					VARCHAR(5)			NULL,
	SizeUnitMeasureCode		CHAR(3)				NULL,
	WeightUnitMeasureCode	CHAR(3)				NULL,
	ProductWeight			DECIMAL(8, 2)		NULL
);

INSERT INTO #ProductTable (ProductName, ProductNumber, Color, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, ProductWeight)
SELECT ProductTable.ProductName
	  ,ProductTable.ProductNumber
	  ,ProductTable.Color
	  ,ProductTable.StandardCost
	  ,ProductTable.ListPrice
	  ,ProductTable.Size
	  ,ProductTable.SizeUnitMeasureCode
	  ,ProductTable.WeightUnitMeasureCode
	  ,ProductTable.ProductWeight
  FROM 
		(
			VALUES ('HL Road Frame - Black, 58', 'FR-R92B-58', 'Black', 1059.31, 1431.5, 58, 'CM ', 'TON ', 2.24)
				  ,('HL Road Frame - Red, 58', 'FR-R92R-58', 'Red', 1059.31, 1431.5, 58, 'DL ', 'LB ', 2.24)
				  ,('HL Road Frame - Red, 62', 'FR-R92R-62', 'Red', 868.6342, 1431.5, 62, 'CM ', 'LB ', 2.3)
				  ,('HL Road Frame - Red, 44', 'FR-R92R-44', 'Red', 868.6342, 1431.5, 44, 'CM ', 'DL ', 2.12)
				  ,('HL Road Frame - Red, 48', 'FR-R92R-48', 'Red', 868.6342, 1431.5, 48, 'DL ', 'LB ', 2.16)
				  ,('HL Road Frame - Red, 52', 'FR-R92R-52', 'Red', 868.6342, 1431.5, 52, 'CM ', 'LB ', 2.2)
				  ,('HL Road Frame - Red, 56', 'FR-R92R-56', 'Red', 868.6342, 1431.5, 56, 'CM ', 'AB ', 2.24)
				  ,('LL Road Frame - Black, 58', 'FR-R38B-58', 'Black', 204.6251, 337.22, 58, 'AB ', 'LB ', 2.46)
				  ,('LL Road Frame - Black, 60', 'FR-R38B-60', 'Black', 204.6251, 337.22, 60, 'CM ', 'DD ', 2.48)
				  ,('LL Road Frame - Black, 62', 'FR-R38B-62', 'Black', 204.6251, 337.22, 62, 'AB ', 'LB ', 2.5)
		) As ProductTable (ProductName, ProductNumber, Color, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, ProductWeight);

/* Use multiple search words with spaces */
DECLARE @SearchString		VARCHAR(MAX) = '';

SELECT @SearchString = 'red';
-- SELECT @SearchString = 'red CM';

/* Remove any single quotes that cause issues */
SELECT @SearchString = REPLACE(@SearchString, '''', '''''');

IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL DROP TABLE #SearchResults;

CREATE TABLE #SearchResults
(
    SearchString			VARCHAR(1024)	NOT NULL,
	ProductName				VARCHAR(50)		NOT NULL,
	ProductNumber			VARCHAR(25)		NOT NULL,
	Color					VARCHAR(15)		NULL,
	StandardCost			MONEY			NOT NULL,
	ListPrice				MONEY			NOT NULL,
	Size					VARCHAR(5)		NULL,
	SizeUnitMeasureCode		CHAR(3)			NULL,
	WeightUnitMeasureCode	CHAR(3)			NULL,
	ProductWeight			DECIMAL(8, 2)	NULL
);

SELECT @SearchStringFinal = 'SearchString LIKE ' + '''%' + STRING_AGG(CONVERT(NVARCHAR(max), Item), '%'' AND SearchString LIKE ''%') + '%'''
FROM dbo.DelimitedSplit8K(@SearchString, ' ') As split;

INSERT INTO #SearchResults (SearchString, ProductName, ProductNumber, Color, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, ProductWeight)
SELECT SUBSTRING((
		pt.ProductName + '' + 
		pt.ProductNumber + '' +
		COALESCE(pt.Color, '') + '' +
		COALESCE(TRY_CAST(pt.StandardCost AS VARCHAR(32)), '') + '' +
		COALESCE(TRY_CAST(pt.ListPrice AS VARCHAR(32)), '') + '' +
		COALESCE(pt.Size, '') + '' +
		COALESCE(pt.SizeUnitMeasureCode, '') + '' +
		COALESCE(pt.WeightUnitMeasureCode, '') + '' + 
		COALESCE(TRY_CAST(pt.ProductWeight AS VARCHAR(32)), '') + '' +
		''
    ), 1, 1024) As SearchString
	  ,pt.ProductName
      ,pt.ProductNumber
	  ,Color
	  ,StandardCost
	  ,ListPrice
	  ,Size
	  ,SizeUnitMeasureCode
	  ,WeightUnitMeasureCode
	  ,ProductWeight
  FROM #ProductTable As pt

SELECT @SQLStatement = 
	'SELECT ProductName, ProductNumber, Color, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, ProductWeight ' +
	'FROM #SearchResults WHERE ' + 
	@SearchStringFinal 

EXEC (@SQLStatement);

Search results with no criteria - all rows:

Search results with one criteria:

Search results with two criteria:

How many columns?

Thanks, I will try to disseminate this but really Excel cannot be used in most of the cases due to its 1m rows limitation among other (trying to even open a 500k file is a nightmare).