Filter like - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Filter like

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-01-21 : 04:42:28

filter data starts with ( LA,CA,FD,MN,WE)..
for single can use like LA% .. how to use for multiple ??

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-21 : 04:56:20

select * from urtable where '%'+'LA,CA,FD,MN,WE'+'%' like '%'+ columnname+ '%'

where la, ca, fd are the names in urcolumn then only it will works

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-01-21 : 04:58:51

this is one way ...
Select * from table where column_name like 'LA%' or column_name like 'CA%' OR column_name like 'FD%'
I cud not get your way ....

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-21 : 05:01:22

declare @tab table(acctID int,Ntext varchar(32))
insert into @tab select 1, 'hello' union all select
1, 'This' union all select
1, 'Is' union all select
2, 'notReal' union all select
2, 'What' union all select
3, 'Final' union all select
4, 'result'

select * from @tab where '%'+'What,notReal,This'+'%' like '%'+ Ntext+ '%'

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-01-21 : 05:26:53

it is great ....
pls can u pls explain ..
in where clause .. where column_name ( u have used filter value - normally it comes after like ) ... what is it ???
What is Ntext ???how it is working...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-21 : 05:31:13

quote:
Originally posted by niranjankumark

it is great ....
pls can u pls explain ..
in where clause .. where column_name ( u have used filter value - normally it comes after like ) ... what is it ???
What is Ntext ???how it is working...

ntext is column name
i had written if given string of values are in ntext column then display the records

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-01-21 : 05:42:54

can u correct this one .. need starting letters .. not getting data

declare @tab table(acctID int,Ntext varchar(32))
insert into @tab select 1, 'hello' union all select
1, 'This' union all select
1, 'Is' union all select
2, 'notReal' union all select
2, 'What' union all select
3, 'Final' union all select
4, 'result'

select * from @tab where ntext like + 'he,wh,fi' + '%'

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-21 : 07:23:35

Hi niranjan,
i don't know is this correct or not

try this once
declare @tab table(acctID int,Ntext varchar(32))
insert into @tab select 1, 'hello' union all select
1, 'This' union all select
1, 'Is' union all select
2, 'notReal' union all select
2, 'What' union all select
3, 'Final' union all select
4, 'result'

declare @str table(data varchar(128))
insert into @str select 'he,wh,fi'

SELECT
replace(SUBSTRING(replace(s.data,',',' '),charindex(' ',replace(s.data,',',' '),v.number),abs(charindex(' ',replace(s.data,',',' '),charindex(' ',replace(s.data,',',' '),v.number)+1)-charindex(' ',replace(s.data,',',' '),v.number))),'"','')as value
into #temp FROM @str AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
and v.number > 0
and v.number <= len(replace(s.data,',',' '))
WHERE substring(' ' + replace(s.data,',',' '), v.number, 1) = ' '

select t.acctid, ntext from @tab t inner join #temp s
on t.ntext like '%'+ ltrim(s.value)+'%'
order by t.acctid

drop table #temp

Padmaja
Starting Member

6 Posts

Posted - 2009-01-21 : 07:39:28

declare @tab table(acctID int,Ntext varchar(32))
insert into @tab select 1, 'hello' union all select
1, 'This' union all select
1, 'Is' union all select
2, 'notReal' union all select
2, 'What' union all select
3, 'Final' union all select
4, 'result'

declare @str varchar(128)
select @str = 'he,wh,fi,th'
select ntext from @tab where '%,'+@str+',%' like '%,'+substring(ntext,1,2)+',%'

padmaja

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-21 : 09:39:21

Other way


select source.columns from your_table as source
inner join
(
	select  'LA' as search_data union all
	select 'CA' union all
	select 'FD' union all
	select 'MN' union all
	select 'WE'
) as search_table
on source.col like search_table.search_data +'%'

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:43:45

quote:
Originally posted by niranjankumark

can u correct this one .. need starting letters .. not getting data

declare @tab table(acctID int,Ntext varchar(32))
insert into @tab select 1, 'hello' union all select
1, 'This' union all select
1, 'Is' union all select
2, 'notReal' union all select
2, 'What' union all select
3, 'Final' union all select
4, 'result'

select * from @tab where ','+ 'he,wh,fi' + ',' like '%,'+ ntext +',%'

it should be as above

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-21 : 23:05:36

quote:
Originally posted by visakh16

quote:
Originally posted by niranjankumark

can u correct this one .. need starting letters .. not getting data

declare @tab table(acctID int,Ntext varchar(32))
insert into @tab select 1, 'hello' union all select
1, 'This' union all select
1, 'Is' union all select
2, 'notReal' union all select
2, 'What' union all select
3, 'Final' union all select
4, 'result'

select * from @tab where ','+ 'he,wh,fi' + ',' like '%,'+ ntext +',%'

it should be as above

hi visakh,
He will give the just two r more letters in a word then he should get the words which are present in that column

jbp_j
Starting Member

24 Posts

Posted - 2009-01-22 : 03:24:12

hi,

try this one

declare @var varchar(100)

set @var = 'la,cd,ka,bh'

select name
from #temp as e
inner join ( select substring(@var,v.number-1, COALESCE(NULLIF(charindex(',',@var,v.number),0),len(@var)+1)-v.number+1) as 'name1'
from master..spt_values v where v.type = 'p'
and substring(', '+@var,v.number,1) = ',')as d on
name like '%'+name1+'%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources