Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,651 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
example : I'm search for some text in columns use (where [all] like 'samei' and 'sudi' and 'majk') only
my table
-----------------column-ID---- column-allnames
1 samei sudi
2 sudi majk
3 jax fadi
4 sudi samei
5 fadi arm
I want result
--------------------column-ID--- column-allnames ---- column-count
1 samei 2
2 sudi 3
3 majk 1
Try something like this:
declare @mytable table ( ID int, [all] varchar(max) )
insert @mytable values
( 1, 'samei sudi' ),
( 2, 'sudi majk' ),
( 3, 'jax fadi' ),
( 4, 'sudi samei' ),
( 5, 'fadi arm' )
select
id,
w as [all],
(select count(*) from @mytable where
[all] like w + ' %' or
[all] like '% ' + w + ' %' or
[all] like '% ' + w or
[all] = w ) as [count]
from (values (1, 'samei'), (2, 'sudi'), (3, 'majk')) t(id, w)
or
select
id,
w as [all],
(select count(*) from @mytable cross apply string_split([all], ' ') where [value] = w ) as [count]
from (values (1, 'samei'), (2, 'sudi'), (3, 'majk')) t(id, w)