Check a substring is exist in string in sql server

Question

I have a column in database that has these values :

##1##,##7##,

##1##,##3##,##5##,

i need to check if ##3##, is exists or not .

i try to use like clause

declare @MyCSharpParameter

set @MyCSharpParameter = '##3##,##4##'

select * from myTable where col like '%@MyCSharpParameter%'

but,it's not work fine because myTable dose't have '##3##,##4##' it's have ##3##,##5##,

any help please to check it as separate numbers and find 3.

I removed the inconsistent database tags. Please tag with the database you a e really using. Then, fix your data model so you are not storing multiple values in a comma-delimited string. That is a SQL antipattern. — Gordon Linoff, Jul 29 '20 at 14:07
If you want to find "##3##' why don't you set your parameter like this `set @MyCSharpParameter = '##3##'` ? — Diogo Magro, Jul 29 '20 at 14:10
@GordonLinoff i'm working on old project and its already built like this . i can't change the data model. — Qais Almomany, Jul 29 '20 at 14:13
@DiogoMagro dear because these values it's come from C# in multi select, the user may select 3 and 4 i need to check if at least one value from the selected values is exists in mytable or not . — Qais Almomany, Jul 29 '20 at 14:16
@QaisAlmomany Ok.. Did you check STRING_SPLIT? you can split you c# parameters by comma and then you have your parameters separated to check what you want — Diogo Magro, Jul 29 '20 at 14:28

score 2 · Answer 1 · answered Jul 29 '20 at 14:33

2

If you are using SQL Server 2016 or above, here is one way using string_split

declare @MyCSharpParameter nvarchar(50);
set @MyCSharpParameter = '##3##,##4##';

create table t (col varchar(100));

insert into t 
values('##1##,##7##'),('##1##,##3##,##5##');

select *, case when b.value is not null then 'yes' else 'no' end as found
from t a 
left join string_split(@MyCSharpParameter, ',') as b on a.col like '%'+b.value+'%';

DEMO

answered Jul 29 '20 at 14:33

Radagast

5,102
3
12
27

i got this message in sql server 2017 " Invalid object name 'string_split'. " – Qais Almomany Jul 30 '20 at 09:37
Does this help? https://stackoverflow.com/q/1362531/9947159 – Radagast Jul 30 '20 at 12:40
And this one https://stackoverflow.com/q/47205829/9947159 – Radagast Jul 30 '20 at 12:43

score 2 · Answer 2 · answered Jul 29 '20 at 14:50

You can use an exists subquery:

select t.*,
       (case when exists (select 1
                          from string_split(a.col, ',') s1 join
                               string_split(@MyCSharpParameter, ',') s2
                               on s1.value = s2.value
                         )
              then 'yes' else 'no'
        end)
from t;

This does not return duplicate rows if there are multiple matches.

Check a substring is exist in string in sql server

2 Answers2