January 7, 2026 at 5:53 pm
Good Evening,
Is there a simpler way to rearrange the following WHERE condition:
[Column_1] LIKE 'Beta08%' OR [Column_1] LIKE 'Beta11%' OR [Column_1] LIKE 'Beta16%' OR [Column_1] LIKE 'Beta17%' OR [Column_1] LIKE 'Beta15%' OR [Column_1] IN ('Beta192')
OR [Column_2] LIKE 'Beta08%' OR [Column_2] LIKE 'Beta11%' OR [Column_2] LIKE 'Beta16%' OR [Column_2] LIKE 'Beta17%' OR [Column_2] LIKE 'Beta15%' OR [Column_2] IN ('Beta192')
OR [Column_3] LIKE 'Beta08%' OR [Column_3] LIKE 'Beta11%' OR [Column_3] LIKE 'Beta16%' OR [Column_3] LIKE 'Beta17%' OR [Column_3] LIKE 'Beta15%' OR [Column_3] IN ('Beta192')
OR [Column_4] LIKE 'Beta08%' OR [Column_4] LIKE 'Beta11%' OR [Column_4] LIKE 'Beta16%' OR [Column_4] LIKE 'Beta17%' OR [Column_4] LIKE 'Beta15%' OR [Column_4] IN ('Beta192')
OR [Column_5] LIKE 'Beta08%' OR [Column_5] LIKE 'Beta11%' OR [Column_5] LIKE 'Beta16%' OR [Column_5] LIKE 'Beta17%' OR [Column_5] LIKE 'Beta15%' OR [Column_5] IN ('Beta192')
OR [Column_6] LIKE 'Beta08%' OR [Column_6] LIKE 'Beta11%' OR [Column_6] LIKE 'Beta16%' OR [Column_6] LIKE 'Beta17%' OR [Column_6] LIKE 'Beta15%' OR [Column_6] IN ('Beta192')
OR [Column_7] LIKE 'Beta08%' OR [Column_7] LIKE 'Beta11%' OR [Column_7] LIKE 'Beta16%' OR [Column_7] LIKE 'Beta17%' OR [Column_7] LIKE 'Beta15%' OR [Column_7] IN ('Beta192')
OR [Column_8] LIKE 'Beta08%' OR [Column_8] LIKE 'Beta11%' OR [Column_8] LIKE 'Beta16%' OR [Column_8] LIKE 'Beta17%' OR [Column_8] LIKE 'Beta15%' OR [Column_8] IN ('Beta192')
OR [Column_9] LIKE 'Beta08%' OR [Column_9] LIKE 'Beta11%' OR [Column_9] LIKE 'Beta16%' OR [Column_9] LIKE 'Beta17%' OR [Column_9] LIKE 'Beta15%' OR [Column_9] IN ('Beta192')
OR [Column_10] LIKE 'Beta08%' OR [Column_10] LIKE 'Beta11%' OR [Column_10] LIKE 'Beta16%' OR [Column_10] LIKE 'Beta17%' OR [Column_10] LIKE 'Beta15%' OR [Column_10] IN ('Beta192')
OR [Column_11] LIKE 'Beta08%' OR [Column_11] LIKE 'Beta11%' OR [Column_11] LIKE 'Beta16%' OR [Column_11] LIKE 'Beta17%' OR [Column_11] LIKE 'Beta15%' OR [Column_11] IN ('Beta192')
OR [Column_12] LIKE 'Beta08%' OR [Column_12] LIKE 'Beta11%' OR [Column_12] LIKE 'Beta16%' OR [Column_12] LIKE 'Beta17%' OR [Column_12] LIKE 'Beta15%' OR [Column_12] IN ('Beta192')
Kind Regards
January 7, 2026 at 7:20 pm
One way I can think of to make it a little more simple to read would be to concat all of the columns together then search for the string BUT that'll be non-sargable so it'll be a performance hit.
I think the BEST way to do it would be to redesign the table so you don't have a situation like that. If the application requires the data to be presented like that, use a view or stored procedure to present it the way the application requires but redesign the table so you don't need to search for the same potential value across multiple columns. If possible, update the app too so it only writes that value to one of the columns. And if the column is always "Beta##", may want to call the column something like Beta Number and you could have it as an int to reduce the size on disk and bandwidth when transferring the data.
But if you are stuck with that and can't do a redesign, I think your options are what you have OR the concat approach I suggested but neither are really pretty. BUT once you toss that into a stored procedure, you don't need to look at it much so might be good enough as is?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 7, 2026 at 7:52 pm
AI gives a few suggestions. If you have the DDL and some sample data, could play around with a few options. I assume this is SQL 2022?
January 7, 2026 at 8:09 pm
Sure redesign if possible. As far as the task at hand it depends on the number of rows really. If there are a good many rows then allocating a temp table and indexing could be useful, and perhaps indexing a persisted computed left6 column (e.g. LEFT(val, 6)) could be beneficial. The question's WHERE conditions treat all columns as the same so to make the query do that too you could VALUES-unpivot column_1, 2, 3, ... using CROSS APPLY and test the first 6 characters against the list of "pre6" strings. Maybe something like this
with pre_cte(pre6) as (
select *
from (values ('Beta08'),('Beta11'),('Beta15'),
('Beta16'),('Beta17')) p(pre6))
select t.*
from dbo.yourtable t
where exists (select 1
from (values (t.column_1),(t.column_2),(t.column_3),
(t.column_4),(t.column_5),(t.column_6),
(t.column_7),(t.column_8),(t.column_9),
(t.column_10),(t.column_11),(t.column_12)) v(val)
cross apply (values (convert(char(6), left(v.val,6)))) x(pre6)
where exists (select 1
from pre_cte p
where p.pre6 = x.pre6)
or v.val = 'Beta192');
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 8, 2026 at 12:51 am
you can do it easily. and without substrings or the likes.
note that the pattern you have a "in" does not contain the % sign at the end of the pattern. so the LIKE pattern works as a "equal"
drop table if exists #sample
select *
into #sample
from (values ('Beta08', 'xxx', 'xxx')
, ('xxx', 'Beta11', 'xxx')
, ('xxx', 'xxx', 'Beta16')
, ('Beta17', 'xxx', 'xxx')
, ('xxx', 'Beta15', 'xxx')
, ('xxx', 'xxx', 'Beta192')
) t(column_1, Column_2, Column_3)
;
with pattern as
(select *
from (values ('Beta08%')
, ('Beta11%')
, ('Beta16%')
, ('Beta17%')
, ('Beta15%')
, ('Beta192')
) t(Pattern)
)
select *
from #sample s1
cross apply (select top 1 *
from pattern p1
where s1.column_1 like p1.Pattern
or s1.Column_2 like p1.Pattern
or s1.Column_3 like p1.Pattern
) p1
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply