quote:

---

Originally posted by khalik

its not as simple as it is Dear..

i have many such rows..i dont know which all country will be there.. if i select all customer from us then i need not apply other such condition like "State='california'" or "City ='san francisco'"
as its will only fetch subset of the first conditions.

======================================
Ask to your self before u ask someone

---

quote:

---

Originally posted by ashishashish

create table test20(id int,condition varchar(2000))

insert into test20
select 1, 'Country =US' union all
select 2, 'Country =US and State=california' union all
select 3, 'Country =US and State=california and City =san francisco' union all
select 4, 'Country =India' union all
select 5, 'Country =India and State=AP' union all
select 6, 'Country =India and State=AP and City =Hyderabad'

select * from test20 where condition not like '%and%'

May Be like this,,,i Think

Thanks,,

---

quote:

---

Originally posted by raky

quote:

---

Originally posted by ashishashish

create table test20(id int,condition varchar(2000))

insert into test20
select 1, 'Country =US' union all
select 2, 'Country =US and State=california' union all
select 3, 'Country =US and State=california and City =san francisco' union all
select 4, 'Country =India' union all
select 5, 'Country =India and State=AP' union all
select 6, 'Country =India and State=AP and City =Hyderabad'

select * from test20 where condition not like '%and%'

May Be like this,,,i Think

Thanks,,

---

If the country name is Southerland or newzealand or phinland or england etc. They won't be returned..isn't it.?

---

quote:

---

Originally posted by ashishashish

quote:

---

Originally posted by raky

quote:

---

Originally posted by ashishashish

create table test20(id int,condition varchar(2000))

insert into test20
select 1, 'Country =US' union all
select 2, 'Country =US and State=california' union all
select 3, 'Country =US and State=california and City =san francisco' union all
select 4, 'Country =India' union all
select 5, 'Country =India and State=AP' union all
select 6, 'Country =India and State=AP and City =Hyderabad'

select * from test20 where condition not like '%and%'

May Be like this,,,i Think

Thanks,,

---

If the country name is Southerland or newzealand or phinland or england etc. They won't be returned..isn't it.?

---

May be Like This..........


insert into test21
select 1, 'Country =US' union all
select 2, 'Country =US and State=california' union all
select 3, 'Country =US and State=california and City =san francisco' union all
select 4, 'Country =India' union all
select 5, 'Country =India and State=AP' union all
select 6, 'Country =India and State=AP and City =Hyderabad' union all
select 7, 'country=newzealand' union all
select 8, 'country=finland'
select * from test21 where condition not like '% and %'

Test it If there is any error comes up then lemme know,,,,

Thanks,,,,

---

quote:

---

Originally posted by khalik

thanks ashishashish and raky

you guy got the solution to the problem, let me add some thign to it.
i may have a case where only the subset is avilable.

7. country 'AE' and state ='Dubai' (not sure abt other country...state)

in this case its only with combination country and state.. so it should return it..

consider the follow generic case also.
1 Country =<@country>
2. Country =<@country> and State= <@state>
3. Country =<@country> and State= <@state> and City =<@city>

for US & India .. i have 1,2,3
for UAE we have only 2
for singapore we have 3

in general if 1 is fetch then 2,3 shd not fetch.
if 2 is fetch then 3 shd not be fetch.
if 1,2 is not fetch then fetch 3

Regards
Khalik

======================================
Ask to your self before u ask someone

---

quote:

---

Originally posted by Jai Krishna

declare @test table ( id int, condition varchar(100))
insert into @test
select 1, 'Country =''US''' union all
select 2, 'Country =''US'' and State=''california''' union all
select 3, 'Country =''US'' and State=''california'' and City =''san francisco''' union all
select 4, 'Country =''India''' union all
select 5, 'Country =''India'' and State=''AP''' union all
select 6, 'Country =''India'' and State=''AP'' and City =''Hyderabad''' union all
select 7, 'Country =''Australia'' and State=''Melbourne''' union all
select 8, 'Country =''Australia'' and State=''AP'' and City =''zzz''' union all
select 9, 'Country =''New Zealand'' and State=''zzz'' and City =''zzz'''

select t.id,t.condition from
(select id,condition,row_number() over(partition by left(condition,case when patindex('% and%',condition) = 0 then len(condition) else patindex('% and%',condition)-1 end) order by id) as rowno from @test) t
where rowno = 1
order by id

Jai Krishna

---

quote:

---

Originally posted by khalik

Hi Raky

can you please explain this part of ur solution. in words. what will be output of substring..

select id,condition, substring(condition,charindex('''',condition)+1,charindex('''',condition,charindex('''',condition)+1)-charindex('''',condition)-1)
from yourtable


======================================
Ask to your self before u ask someone

---