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
– Gordon Linoff, Commented Jul 29, 2020 at 14:07
If you want to find "##3##' why don't you set your parameter like this set @MyCSharpParameter = '##3##' ? — Diogo Magro
– Diogo Magro, Commented Jul 29, 2020 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
– Qais Almomany, Commented Jul 29, 2020 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
– Qais Almomany, Commented Jul 29, 2020 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
– Diogo Magro, Commented Jul 29, 2020 at 14:28

Rajat · Accepted Answer · 2020-07-29 14:33:37Z

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, 2020 at 14:33

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Qais Almomany Over a year ago

i got this message in sql server 2017 " Invalid object name 'string_split'. "

Rajat Over a year ago

Does this help? stackoverflow.com/q/1362531/9947159

Rajat Over a year ago

And this one stackoverflow.com/q/47205829/9947159

Gordon Linoff · Accepted Answer · 2020-07-29 14:50:11Z

2

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.

answered Jul 29, 2020 at 14:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Check a substring is exist in string in sql server

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related