SQL Server SELECT where any column contains 'x'
First Method(Tested)
First get list of columns in string variable separated by commas and then you can search 'foo' using that variable by use of IN
Check stored procedure below which first gets columns and then searches for string:
DECLARE @TABLE_NAME VARCHAR(128)
DECLARE @SCHEMA_NAME VARCHAR(128)
-----------------------------------------------------------------------
-- Set up the name of the table here :
SET @TABLE_NAME = 'testing'
-- Set up the name of the schema here, or just leave set to 'dbo' :
SET @SCHEMA_NAME = 'dbo'
-----------------------------------------------------------------------
DECLARE @vvc_ColumnName VARCHAR(128)
DECLARE @vvc_ColumnList VARCHAR(MAX)
IF @SCHEMA_NAME =''
BEGIN
PRINT 'Error : No schema defined!'
RETURN
END
IF NOT EXISTS (SELECT * FROM sys.tables T JOIN sys.schemas S
ON T.schema_id=S.schema_id
WHERE T.Name=@TABLE_NAME AND S.name=@SCHEMA_NAME)
BEGIN
PRINT 'Error : The table '''+@TABLE_NAME+''' in schema '''+
@SCHEMA_NAME+''' does not exist in this database!'
RETURN
END
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT CASE WHEN PATINDEX('% %',C.name) > 0
THEN '['+ C.name +']'
ELSE C.name
END
FROM sys.columns C
JOIN sys.tables T
ON C.object_id = T.object_id
JOIN sys.schemas S
ON S.schema_id = T.schema_id
WHERE T.name = @TABLE_NAME
AND S.name = @SCHEMA_NAME
ORDER BY column_id
SET @vvc_ColumnList=''
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @vvc_ColumnName
WHILE @@FETCH_STATUS=0
BEGIN
SET @vvc_ColumnList = @vvc_ColumnList + @vvc_ColumnName
-- get the details of the next column
FETCH NEXT FROM TableCursor INTO @vvc_ColumnName
-- add a comma if we are not at the end of the row
IF @@FETCH_STATUS=0
SET @vvc_ColumnList = @vvc_ColumnList + ','
END
CLOSE TableCursor
DEALLOCATE TableCursor
-- Now search for `foo`
SELECT *
FROM testing
WHERE 'foo' in (@vvc_ColumnList );
2nd Method
In sql server you can get object id of table then using that object id you can fetch columns. In that case it will be as below:
Step 1: First get Object Id of table
select * from sys.tables order by name
Step 2: Now get columns of your table and search in it:
select * from testing where 'foo' in (select name from sys.columns where object_id =1977058079)
Note: object_id is what you get fetch in first step for you relevant table
SQL SELECT WHERE field contains words
Rather slow, but working method to include any of words:
SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
OR column1 LIKE '%word2%'
OR column1 LIKE '%word3%'
If you need all words to be present, use this:
SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
AND column1 LIKE '%word2%'
AND column1 LIKE '%word3%'
If you want something faster, you need to look into full text search, and this is very specific for each database type.
Bigquery Select from table where any column contains 'FINISHED'
Below is for BigQuery Standard SQL
Should be good start for you :)
#standardSQL
SELECT <columns to output>
FROM yourTable AS t
WHERE REGEXP_CONTAINS(LOWER(TO_JSON_STRING(t)), 'finished')
You can test/play with it with below dummy data
#standardSQL
WITH yourTable AS (
SELECT 'a' AS x, 'b' AS y, 'c' AS z UNION ALL
SELECT 'finished', '', '' UNION ALL
SELECT '', 'Bigquery Select from table where any column contains "FINISHED"','' UNION ALL
SELECT '', '', 'aaa' UNION ALL
SELECT 'finished', 'bbb', 'finished'
)
SELECT *
FROM yourTable AS t
WHERE REGEXP_CONTAINS(LOWER(TO_JSON_STRING(t)), 'finished')
Update
Note: if you have your search word as a part of at least one column name - above will return all rows! To address this - you would need to invest a little bit more coding
For example, for simple schema (with no record or repeated) this would be a
#standardSQL
SELECT <columns to output>
FROM yourTable AS t
WHERE (SELECT COUNTIF(SPLIT(zzz, ':')[SAFE_OFFSET(1)] LIKE '%finished%')
FROM UNNEST(SPLIT(SUBSTR(LOWER(TO_JSON_STRING(t)),2,LENGTH(TO_JSON_STRING(t))-2))) AS zzz
) > 0
You can test this with below
#standardSQL
WITH yourTable AS (
SELECT 'a' AS x, 'b' AS y, 'c' AS col_finished UNION ALL
SELECT 'finished', '', '' UNION ALL
SELECT '', 'Bigquery Select from table where any column contains "FINISHED"','' UNION ALL
SELECT '', '', 'aaa' UNION ALL
SELECT 'finished', 'bbb', 'finished'
)
SELECT *
FROM yourTable AS t
WHERE (SELECT COUNTIF(SPLIT(zzz, ':')[SAFE_OFFSET(1)] LIKE '%finished%')
FROM UNNEST(SPLIT(SUBSTR(LOWER(TO_JSON_STRING(t)),2,LENGTH(TO_JSON_STRING(t))-2))) AS zzz
) > 0
SQL Return rows where column 1 contains X and Y
Here's one way to do it. Use an EXISTS predicate that uses a correlated subquery to check for the existence of the "other" row. This assumes that you only want to return rows that have values of 'Email' and 'Appointment' in the Activity column, and to exclude rows that have any other value for activity. (This isn't the most efficient way to do it.)
SELECT t.account
, t.activity
FROM example_table t
WHERE t.activity IN ('Email','Appointment')
AND EXISTS ( SELECT 1
FROM example_table d
WHERE d.account = t.account
AND d.activity =
CASE t.activity
WHEN 'Email' THEN 'Appointment'
WHEN 'Appointment' THEN 'Email'
END
)
ADDITION
Here's the approach above applied to the original query (which is later supplied as a comment on another answer...)
SELECT t.ACCOUNT
, t.COMPANY
, h.RESPONSE
, h.DAT_ AS Resp_Date
, h.USERNAME
, DATE_FORMAT(p.ENDDATE,'%M') AS Renewal
, t.OWNER
, h.CustomerStatus
, h.Contact
, a.ANAL14 AS APPDATE
FROM TELRCMxxx t
JOIN TELCOMxxx h
ON t.ACCOUNT = h.ACCOUNT
AND h.ACTIVITY in ('Appointment','email')
AND EXISTS
( SELECT 1
FROM TELCOMxxx b
WHERE b.ACCOUNT = h.ACCOUNT
AND b.ACTIVITY = CASE h.ACTIVITY
WHEN 'email' THEN 'Appointment'
WHEN 'Appointment' THEN 'email'
END
)
LEFT
JOIN ACCSTOxxx p
ON t.ACCOUNT = p.ACCOUNT
LEFT
JOIN RCMANLxxx a
ON t.ACCOUNT = a.ACCOUNT
NOTES: original query has a LEFT join to h, but the "outerness" of the join operation is negated by the WHERE clause, which effectively verifies h.ACTIVITY IS NOT NULL. The LEFT keyword is removed, and the h.ACTIVITY IN ('Appointment','email') predicate is moved from the WHERE clause to the ON clause of the join. But that doesn't really change anything about the query.
The change to the query is the addition of an "EXISTS" predicate that checks for the existence of another row in h, matching on ACCOUNT, and matching either 'Appointment' or 'email', as the opposite of the value in the row from h being checked.
Note that this:
AND b.ACTIVITY = CASE h.ACTIVITY
WHEN 'email' THEN 'Appointment'
WHEN 'Appointment' THEN 'email'
END
is equivalent to:
AND ( ( b.ACIVITY = 'email' AND h.ACTIVITY = 'Appointment' )
OR
( b.ACIVITY = 'Appointment' AND h.ACTIVITY = 'email' )
)
END ADDITION
If you need to return all rows for the account, including other values for activity, then you remove the t.Activity IN predicate from the WHERE clause on the outer query, and just check for the existence of both an 'Email' and 'Appointment' row for that account:
SELECT t.account
, t.activity
FROM example_table t
WHERE EXISTS ( SELECT 1
FROM example_table e
WHERE e.account = t.account
AND e.activity = 'Email'
)
AND EXISTS ( SELECT 1
FROM example_table a
WHERE a.account = t.account
AND a.activity = 'Appointment'
)
This is not the most efficient approach, but it will return the specified result.
For large sets, a (usually) more efficient approach is to use a JOIN operation.
To get a list of distinct account values that have both 'Email' and 'Appointment' rows:
SELECT e.account
FROM example_table e
JOIN example_table a
ON a.account = e.account
AND a.activity = 'Appointment'
WHERE e.activity = 'Email'
GROUP BY e.account
To get all rows from the table for those accounts:
SELECT t.account
, t.activity
FROM example_table t
JOIN ( SELECT e.account
FROM example_table e
JOIN example_table a
ON a.account = e.account
AND a.activity = 'Appointment'
WHERE e.activity = 'Email'
GROUP BY e.account
) s
ON s.account = t.account
If you only want to return rows with particular values for activity, you can add a WHERE clause, e.g.
WHERE t.activity IN ('Email','Appointment','Foo')
How can I search all columns in a table?
SELECT ...
FROM yourtable
WHERE 'val' IN (field1, field2, field3, field4, ...)
if you're looking for exact full-field matches. If you're looking for substring matches, you'll have to go about it the long way:
WHERE field1 LIKE '%val%' or field2 LIKE '%val%' etc....
Identify all columns containing only a single value (e.g. 0,0.00, Z, Y, N)
Here's one way you could go about this. For each column of the specified table, it does a count of rows that match your criteria and, if the column's row count matches that of the table's, the column is added to the XML list for review.
NOTE:
Given your number of columns and possible row counts, this process could take a while.
DECLARE @Columns table ( column_name varchar(255), pk INT IDENTITY(1,1) );
DECLARE
@TableName varchar(50) = 'tblClients',
@Criteria varchar(255) = 'IN (''0'', ''0.00'', ''Z'', ''N'', ''Y'')',
@sql nvarchar(MAX),
@rows int;
/* Get the current row count for @TableName */
SET @sql = 'SELECT @count = COUNT( DISTINCT [' + @col + '] ) FROM ' + @TableName + ';';
EXEC sp_executesql @sql, N'@count int OUT', @count = @rows OUT;
/* Create an XML object to store fields that match criteria */
DECLARE @List xml = '<root><columns></columns></root>';
-- Insert the table name and number of rows processed.
SET @List.modify(
'insert
<table>
<name>{sql:variable("@TableName")}</name>
<rows>{sql:variable("@rows")}</rows>
<criteria>{sql:variable("@Criteria")}</criteria>
</table>
as first
into (//root)[1]'
);
/* Collect a list of column names for the specified table */
INSERT INTO @Columns ( column_name )
SELECT [name] FROM sys.columns WHERE object_id = OBJECT_ID( @TableName ) ORDER BY column_id;
/* For-each column, check if it contains: [ 0 | 0.00 | Z | N | Y ] */
DECLARE @pk int = 1, @col varchar(255), @count int;
WHILE @pk <= ( SELECT MAX ( pk ) FROM @Columns )
BEGIN
-- Current column.
SELECT @col = column_name FROM @Columns WHERE pk = @pk;
-- Get count of rows for this column with the specified values.
SET @sql = 'SELECT @count = COUNT( DISTINCT [' + @col + '] ) FROM ' + @TableName + ' WITH (NOLOCK) WHERE CAST( [' + @col + '] AS varchar(MAX) ) ' + @Criteria + ';';
EXEC sp_executesql @sql, N'@count int OUT', @count = @count OUT;
-- Insert the column for review if its count matches the table row count.
IF @count BETWEEN 1 AND 2
SET @List.modify(
'insert
<column>
<name>{sql:variable("@col")}</name>
<count>{sql:variable("@count")}</count>
</column>
into (//root/columns)[1]'
);
-- Next column.
SET @pk = @pk + 1;
END
/* SELECT @List results */
SELECT @List AS List;
In my case, the following XML is returned. These columns match your criteria of ones that should be reviewed.
<root>
<table>
<name>tblClients</name>
<rows>143</rows>
<criteria>IN ('0', '0.00', 'Z', 'N', 'Y')</criteria>
</table>
<columns>
<column>
<name>WebPortal</name>
<count>143</count>
</column>
<column>
<name>EnforceQTY</name>
<count>143</count>
</column>
<column>
<name>CheckForPrepaid</name>
<count>143</count>
</column>
<column>
<name>ReportNet</name>
<count>143</count>
</column>
<column>
<name>ActiveClient</name>
<count>143</count>
</column>
</columns>
</root>
No null values in any column in SQL Server
This should do it
SET NOCOUNT ON;
-- populate table
create table my_test_table
(
foo1 int,
foo2 int,
foo3 int,
foo4 int,
foo5 int
)
INSERT INTO my_test_table
select 1,2,3,4,5
INSERT INTO my_test_table
select 1,2,null,4,5
INSERT INTO my_test_table
select 1,2,3,4,null
INSERT INTO my_test_table
select 1,null,3,4,null
-- Actual query needed for question
declare @columns table
(
ident int identity(1,1),
column_name varchar(255)
)
INSERT INTO @columns (column_name)
SELECT c.[name]
FROM sys.columns c
INNER JOIN sys.objects o ON (c.object_id = o.object_id)
WHERE o.[name] = 'my_test_table'
ORDER BY c.column_id
declare @table_count int = (select COALESCE(count(ident),0) from @columns)
declare @counter int = 1
declare @column_name varchar(255)
declare @sql nvarchar(max) = ''
while (@table_count > 0 AND @counter <= @table_count)
BEGIN
select @column_name = column_name
from @columns
where ident = @counter
set @sql += ', count(' + @column_name + ') as [' + @column_name + '_total]'
set @counter += 1
END
exec('select count(*) as total' + @sql + ' from my_test_table')
make sure you change the name of "my_test_table" to whatever the table name is.
Result
total | foo1_total |foo2_total |foo3_total |foo4_total |foo5_total
--------+-------------+--------------+-------------+-------------+-----------
4 | 4 | 3 | 3 | 4 | 2
where the column total doesn't match the total (first column) then it has some NULL's.
TEST: http://rextester.com/AVB29103
Related Topics
Should I Use SQL_Variant Data Type
Differencebetween 'Yyyy' and 'Rrrr' in Oracle SQL
Transform and Pivot in Access 2013 SQL
Finding Rows That Don't Contain Numeric Data in Oracle
How to Use Timestamp_To_Scn and Scn_To_Timestamp in Oracle
Divide by Zero/Null Workaround in Ssrs 2008 Report
SQL Server Identity Column Values Start at 0 Instead of 1
What Is Causing Foreign Key Mismatch Error
Does 'Select' Always Order by Primary Key
Mysql: "The Select Would Examine More Than Max_Join_Size Rows"
Function for Week of the Month in MySQL
How to Separate String into Different Columns
Aggregating (X,Y) Coordinate Point Clouds in Postgresql
Count Returning Blank Instead of 0
Select from Table with Varying in List in Where Clause