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).