check columns have same value in single row 1

[tirf]

How do I check if all columns in a single row have same value

(COALESCE kind of function) but in this case all column same value

foe example

COL1 COl2
1 1
2 3
4 4

my result set should return

COL1 COl2
1 1
4 4

Thanks

 

[Qik3Coder]

Select ...
FROM ...
WHERE col1 = col2

LOD

You've got questions and source code. We want both!

 

[tirf]

I have 15 columns in the table and equating each coulmn is a bit hard, I was checking if we have any built-in function

Thanks

 

[Qik3Coder]

You didn't say you had 15 columns, you asked what appeared to be a basic question.

Declare @_tbl Table (
row_id int identity(1,1),
col1 varchar(5),
col2 varchar(5),
col3 varchar(5),
col4 varchar(5),
col5 varchar(5))

INSERT INTO @_tbl
Select 'A', 'a', 'a', 'a', 'a' UNION ALL 
Select 'B', 'a', 'a', 'a', 'a' UNION ALL 
Select 'B', 'C', 'a', 'a', 'a' UNION ALL 
Select 'A', 'D', 'E', 'a', 'a' 

SELECT * FROM @_tbl
WHERE COL1 = COALESCE(NULLIF(col2,col1),NULLIF(col3,col1),NULLIF(col4,col1),NULLIF(col5,col1), col1)

Lodlaiden

You've got questions and source code. We want both!

 

[tirf]

perfect

Thanks very much Lodlaiden

 

[Qik3Coder]

Welcome. There may be a cleaner way of doing that, but it works.

I used to have my signature read:
The solution to your problem is not the answer to your question.

Lod

You've got questions and source code. We want both!

 

[gmmastros]

I would like to mention one potential problem with this approach. If your columns allow NULL and you have NULLS, then NULLs will be ignored.

When I mean is... suppose COL1 = 'A' and all of the other columns have NULL. It will be returned by this query. However, if COL1 is NULL and all of the other columns have the same value, it will not be returned. This leads to an inconsistent behavior in the query.

If your columns do not allow NULLS, then the query suggested by Lod will work just fine.

Ex:

Declare @_tbl Table (
row_id int identity(1,1),
col1 varchar(5),
col2 varchar(5),
col3 varchar(5),
col4 varchar(5),
col5 varchar(5))

INSERT INTO @_tbl
Select 'A', 'a', 'a', 'a', 'a' UNION ALL 
Select NULL, 'a', 'a', 'a', 'a' UNION ALL 
Select 'A', NULL, NULL, NULL, NULL UNION ALL 
Select 'B', 'a', 'a', 'a', 'a' UNION ALL 
Select 'B', 'C', 'a', 'a', 'a' UNION ALL 
Select 'A', 'D', 'E', 'a', 'a' 

SELECT * FROM @_tbl
WHERE COL1 = COALESCE(NULLIF(col2,col1),NULLIF(col3,col1),NULLIF(col4,col1),NULLIF(col5,col1), col1)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

I was thinking about this after, and it is potentially an interesting way to do data integrity checking in a database with no actual keys.


You've got questions and source code. We want both!