CODEWITHSUNDEEP
sqlsql-server-2008
Pablo Francisco Pérez Hidalgo
Pablo Francisco Pérez Hidalgo

Reputation: 27702

Compare to all columns

Is it possible to compare a vector of values with all columns of a table in SQL, more specifically in MS SQL Server?

For example, I have a table, EXAMPLE, with 3 columns:

EXAMPLE: ColA, ColB, ColC

And I want to check if its columns match a specific vector: ('val0', 'val1', 'val2')

I know I can do that with a sentence like this:

SELECT * FROM EXAMPLE WHERE ColA='val0' AND ColB = 'val1' AND ColC = 'val2'

But I'd like to know if there is some function, ALLEQUAL which could allow me to do something like:

SELECT * FROM EXAMPLE WHERE ALLEQUAL('val0', 'val1', 'val2');

I understand that if that function exists its syntax may be quite different between different RDBMSs and I now focused on Ms SQL Server. Nevertheless I will be more than happy if you can give me examples in other databases managers.

Upvotes: 2

Views: 1390

Answers (3)

Louis Ricci
Louis Ricci

Reputation: 21086

In a well designed database the tables will have a PRIMARY KEY, the key will be a unique identifier for the row, because of this there is no need to match the rest of the 'vector' in your scenario.

Now if you have some flat de-normalized data you could create an sproc that generates dynamic sql to do what you want. You'd have to query INFORMATION_SCHEMA.columns or sys.columns and generate something similar to the first SQL statement you wrote ~"WHERE ColA='val0' AND ColB = 'val1' AND ColC = 'val2' "

Here's the varchar column only approach using FOR XML PATH('')

BEGIN
DECLARE @tbl TABLE(c1 VARCHAR(max), c2 VARCHAR(max), c3 VARCHAR(max))
DECLARE @vec TABLE(data VARCHAR(max))
INSERT INTO @tbl VALUES ('abc', '123', 'xyz'), ('cba', '321', 'zyx')
INSERT INTO @vec VALUES ('abc'), ('123'), ('xyz')
SELECT * FROM @tbl
WHERE c1+c2+c3 = (SELECT '' + data FROM @vec FOR XML PATH(''))
END

Upvotes: 0

HABO
HABO

Reputation: 15816

declare @Foo as Table ( ColA Int, ColB Int );
insert into @Foo ( ColA, ColB ) values ( 1, 1 ), ( 1, 2 ), ( 2, 1 );
select * from @Foo;

select *
  from @Foo
  intersect
  select *
    from ( values ( 2, 1 ) ) as Bar( ColA, ColB );

Upvotes: 1

Robert
Robert

Reputation: 25753

Maybe this will help you

SELECT * 
FROM EXAMPLE 
WHERE ColA+ColB+ColC = 'val0'+'val1'+'val2'

Upvotes: 1

Related Questions