Just Match At Middle String - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Just Match At Middle String

Author

Topic

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-24 : 03:19:41

Hi,
I need retrieve only strings that hold the sub-string in middle. no first no last just middle.

See: the sub-string is "test"

Yes: test test test
Yes: take test test
Yes: test test take
Yes: take test take
No: test test
No: test take take
No: take take test
No: test

please solve it for me.

I prepare sample data for you.

declare @sample table(s varchar(50));
insert @sample values
('test test test'),
('test test'),
('test'),
('test take test'),
('test take take'),
('take test test'),
('take test take');

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-24 : 04:14:02

[code]
select case when s like ('% test %') then 'Yes' else 'No' end
from @sample
[/code]

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-24 : 05:04:44

Thanks,
But how about this:

declare @sample table(s varchar(50));
insert @sample values
('testtesttest'), --yes
('testtest'),
('test'),
('testtaketest'),
('testtaketake'),
('taketesttest'),--yes
('taketesttake');--yes

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-24 : 09:03:09

quote:
Originally posted by sigmas

Thanks,
But how about this:

declare @sample table(s varchar(50));
insert @sample values
('testtesttest'), --yes
('testtest'),
('test'),
('testtaketest'),
('testtaketake'),
('taketesttest'),--yes
('taketesttake');--yes

Try this:


select case when substring(s,2,length(s)-2) like ('%test%') then 'Yes' else 'No' end
  from @sample

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-25 : 04:31:41

how about this?


select *
from @sample
where s like '%[!test]%test%[!test]%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources