SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
 Jul 25 2020 11:26:55 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table mytable(id varchar(20), name varchar(50));

insert into mytable values('302345','Name1')
insert into mytable values('522345','Name2')
insert into mytable values('1X2345','Name3')
insert into mytable values('2X2345','Name4')
insert into mytable values('1X8765','Name5')
insert into mytable values('2X2123','Name6')
insert into mytable values('502345','Name7')
insert into mytable values('M62345','Name8')
8 rows affected

select * from mytable


id
name


302345
Name1

522345
Name2

1X2345
Name3

2X2345
Name4

1X8765
Name5

2X2123
Name6

502345
Name7

M62345
Name8


select * from mytable inner join (
SELECT '30%' Prefix
UNION ALL SELECT '50%'
UNION ALL SELECT '1X%')list on id not like prefix


id
name
Prefix


302345
Name1
50%

302345
Name1
1X%

522345
Name2
30%

522345
Name2
50%

522345
Name2
1X%

1X2345
Name3
30%

1X2345
Name3
50%

2X2345
Name4
30%

2X2345
Name4
50%

2X2345
Name4
1X%

1X8765
Name5
30%

1X8765
Name5
50%

2X2123
Name6
30%

2X2123
Name6
50%

2X2123
Name6
1X%

502345
Name7
30%

502345
Name7
1X%

M62345
Name8
30%

M62345
Name8
50%

M62345
Name8
1X%

id	name
302345	Name1
522345	Name2
1X2345	Name3
2X2345	Name4
1X8765	Name5
2X2123	Name6
502345	Name7
M62345	Name8