January 3, 2022 at 6:46 pm
I got a table with 5 integer columns and one varchar(100) column.
Select * from tbl1 where col1 = col2 or ....col5=col4 .....
I dont want to do this. Can we concatenate these fields and somehow comeup with a RegEx or some other way to find the rows that have the same integer values across columns col1-col5
January 3, 2022 at 6:56 pm
What's wrong with just?
COL_ONE = COL_TWO AND COL_ONE=COL_THREE etc....
Any other "clever" solution is almost certainly going to be more code.
January 3, 2022 at 6:58 pm
Deleted
January 3, 2022 at 7:12 pm
Doesn't feel like a 'nice' solution, but this might do it.
DROP TABLE IF EXISTS #t;
CREATE TABLE #t
(
Id VARCHAR(100)
,C1 INT
,C2 INT
,C3 INT
,C4 INT
,C5 INT
);
INSERT #t
(
Id
,C1
,C2
,C3
,C4
,C5
)
VALUES
('a', 1, 2, 3, 4, 5)
,('b', 1, 2, 2, 2, 2)
,('c', 100, 100, 100, 100, 100);
SELECT *
FROM #t t
WHERE CONCAT(t.C1, t.C2, t.C3, t.C4, t.C5) = CONCAT(t.C1, t.C1, t.C1, t.C1, t.C1);
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 3, 2022 at 9:55 pm
CONCAT is not a safe way to do this because different values could appear to be the same.
Do all the columns have to match? Or just some?
For now, I'll assume it's all. But I'll use a query that can be easily adjusted to check for any number of matching values.
SELECT t.*
FROM #t t
CROSS APPLY (
SELECT 1 AS cols_matched
FROM ( VALUES(t.col1), (t.col2), (t.col3), (t.col4), (t.col5)) AS cols(col)
HAVING MIN(col) = MAX(col)
) AS ca1
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 4, 2022 at 10:49 am
CONCAT is not a safe way to do this because different values could appear to be the same.
Do all the columns have to match? Or just some?
For now, I'll assume it's all. But I'll use a query that can be easily adjusted to check for any number of matching values.
This solution does not handle the case where one or more of the columns is NULL. To do that, a slight tweak is required.
SELECT t.*
FROM #t t
CROSS APPLY (
SELECT 1 AS cols_matched
FROM ( VALUES(t.col1), (t.col2), (t.col3), (t.col4), (t.col5)) AS cols(col)
HAVING MIN(col) = MAX(col) AND COUNT(cols.col) = 5
) AS ca1
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 11, 2022 at 6:18 am
What's wrong with just?
COL_ONE = COL_TWO AND COL_ONE=COL_THREE etc....Any other "clever" solution is almost certainly going to be more code.
And, usually slower. I haven't tested it for performance but I can't see another solution being any faster.
--Jeff Moden
January 12, 2022 at 4:09 pm
ScottPletcher wrote:CONCAT is not a safe way to do this because different values could appear to be the same.
Do all the columns have to match? Or just some?
For now, I'll assume it's all. But I'll use a query that can be easily adjusted to check for any number of matching values.
This solution does not handle the case where one or more of the columns is NULL. To do that, a slight tweak is required.
SELECT t.*
FROM #t t
CROSS APPLY (
SELECT 1 AS cols_matched
FROM ( VALUES(t.col1), (t.col2), (t.col3), (t.col4), (t.col5)) AS cols(col)
HAVING MIN(col) = MAX(col) AND COUNT(cols.col) = 5
) AS ca1
It should handle the case where ALL values are NULL. Based on the OP's description, I thought that was the only possible NULL issue that could come up :-), although it's still a good idea to add a check for the COUNT().
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 12, 2022 at 4:32 pm
>> I got a table with 5 integer columns and one varchar(100) column. <<
Where is the DDL? I guess you want us to do everything for you , including read your mind
CREATE TABLE Foobar
(foobar_string VARCHAR (100) NOT NULL PRIMARY KEY,
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
col4 INTEGER NOT NULL,
col5 INTEGER NOT NULL);
Did I guess the key of this nameless table correctly? Are these five columns not null? No defaults? No check constraints? The reason I'm asking is that if they're all positive numbers, the need to do some simple integer arithmetic
(c1 = ((c1 +c2 + c3 + c4 + c5)/5 ).
>> I don't want to do this. Can we concatenate these fields [sic: columns are not fields in SQL] and somehow come up with a RegEx or some other way to find the rows that have the same integer values across columns col1-col5 <<
Regular expressions are meant for strings, but you just told us these columns are integers, a kind of numeric value. COBOL is the only language I know of that uses strings for numeric values.
I'm also curious if these five columns are truly totally different attributes, as it should be a normalized table, for this is a repeated group attempting to imitate an array. Until we have more information, Jeff's "brute force" solution is the safest and probably really fast
Please post DDL and follow ANSI/ISO standards when asking for help.
January 12, 2022 at 11:07 pm
>> I got a table with 5 integer columns and one varchar(100) column. <<
Where is the DDL? I guess you want us to do everything for you , including read your mind
CREATE TABLE Foobar (foobar_string VARCHAR (100) NOT NULL PRIMARY KEY, col1 INTEGER NOT NULL, col2 INTEGER NOT NULL, col3 INTEGER NOT NULL, col4 INTEGER NOT NULL, col5 INTEGER NOT NULL);
Did I guess the key of this nameless table correctly? Are these five columns not null? No defaults? No check constraints? The reason I'm asking is that if they're all positive numbers, the need to do some simple integer arithmetic (c1 = ((c1 +c2 + c3 + c4 + c5)/5 ).
>> I don't want to do this. Can we concatenate these fields [sic: columns are not fields in SQL] and somehow come up with a RegEx or some other way to find the rows that have the same integer values across columns col1-col5 <<
Regular expressions are meant for strings, but you just told us these columns are integers, a kind of numeric value. COBOL is the only language I know of that uses strings for numeric values.
I'm also curious if these five columns are truly totally different attributes, as it should be a normalized table, for this is a repeated group attempting to imitate an array. Until we have more information, Jeff's "brute force" solution is the safest and probably really fast
Thanks for the shout out for the "brute force" method.
Shifting gears a bit, although the alternate method you propose looks easy, it could lead you to being stuck in deep Kimchi if the 5 columns add up to more than what an INTEGER datatype can handle. It's also possible that the sum of the columns divided by 5 can still equal the value of the first column even though none of the other columns have the same value. I strongly recommend NOT using the method you propose even if they're all guaranteed to be non-zero/positive integers.
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply