search in only non tagged words

23 Jun 2006 12:26 PM

shahdharti

Hi

I want to search from table for rows where value should be part of
text , but search should omit values from tag.

like
create table #t (data varchar(8000))

insert into #t values('<div> this is a div text </div>')
insert into #t values('<div> this is a text </div>')
insert into #t values('<div> this is < div </div>')

now when i search for 'div' it should omit the words in tag < ...> and
search in non tag words only

so for query

select * from #t where data like '%div%'

so result should be

<div> this is a div text </div>
<div> this is < div </div>

because here div is also out of tags<....>

but result should not be

<div> this is a text </div>

because here div is only in tags<...>

23 Jun 2006 1:45 PM

Damien

Try this:

select * from #t where data like '%[^<]%div%[^>]%'

Let me know how you get on.

Damien

Show quote

"shahdha***@gmail.com" wrote:

> Hi
>
> I want to search from table for rows where value should be part of
> text , but search should omit values from tag.
>
> like
> create table #t (data varchar(8000))
>
> insert into #t values('<div> this is a div text </div>')
> insert into #t values('<div> this is a text </div>')
> insert into #t values('<div> this is < div </div>')
>
> now when i search for 'div' it should omit the words in tag < ...> and
> search in non tag words only
>
> so for query
>
> select * from #t where data like '%div%'
>
> so result should be
>
> <div> this is a div text </div>
> <div> this is < div </div>
>
> because here div is also out of tags<....>
>
> but result should not be
>
> <div> this is a text </div>
>
> because here div is only in tags<...>
>
>

23 Jun 2006 3:12 PM

shahdharti

Damien wrote:

Show quote

> Try this:
>
> select * from #t where data like '%[^<]%div%[^>]%'
>
>
> Let me know how you get on.
>
> Damien
>
> "shahdha***@gmail.com" wrote:
>
> > Hi
> >
> > I want to search from table for rows where value should be part of
> > text , but search should omit values from tag.
> >
> > like
> > create table #t (data varchar(8000))
> >
> > insert into #t values('<div> this is a div text </div>')
> > insert into #t values('<div> this is a text </div>')
> > insert into #t values('<div> this is < div </div>')
> >
> > now when i search for 'div' it should omit the words in tag < ...> and
> > search in non tag words only
> >
> > so for query
> >
> > select * from #t where data like '%div%'
> >
> > so result should be
> >
> > <div> this is a div text </div>
> > <div> this is < div </div>
> >
> > because here div is also out of tags<....>
> >
> > but result should not be
> >
> > <div> this is a text </div>
> >
> > because here div is only in tags<...>
> >
> >

your query returns also rows
like

<abc div> this is </abc>
or
<abc div dev> this is </abc>

this should not be return for div.

23 Jun 2006 9:17 PM

Hugo Kornelis

On 23 Jun 2006 05:26:51 -0700, shahdha***@gmail.com wrote:

Show quote

>Hi
>
>I want to search from table for rows where value should be part of
>text , but search should omit values from tag.
>
>like
>create table #t (data varchar(8000))
>
>insert into #t values('<div> this is a div text </div>')
>insert into #t values('<div> this is a text </div>')
>insert into #t values('<div> this is < div </div>')
>
>now when i search for 'div' it should omit the words in tag < ...> and
>search in non tag words only
>
>so for query
>
>select * from #t where data like '%div%'
>
>so result should be
>
><div> this is a div text </div>
><div> this is < div </div>
>
>because here div is also out of tags<....>
>
>but result should not be
>
><div> this is a text </div>
>
>because here div is only in tags<...>

Hi shahdharti,

If you're on SQL Server 2005, consider using the xml datatype and an
xquery expression.

Otherwise, try if this works:
SELECT data
FROM #t
WHERE REPLACE(REPLACE(data, '</div>', ''), '<div>', '') LIKE '%div%'

--
Hugo Kornelis, SQL Server MVP

24 Jun 2006 11:05 AM

shahdharti

Hugo Kornelis wrote:

Show quote

> On 23 Jun 2006 05:26:51 -0700, shahdha***@gmail.com wrote:
>
> >Hi
> >
> >I want to search from table for rows where value should be part of
> >text , but search should omit values from tag.
> >
> >like
> >create table #t (data varchar(8000))
> >
> >insert into #t values('<div> this is a div text </div>')
> >insert into #t values('<div> this is a text </div>')
> >insert into #t values('<div> this is < div </div>')
> >
> >now when i search for 'div' it should omit the words in tag < ...> and
> >search in non tag words only
> >
> >so for query
> >
> >select * from #t where data like '%div%'
> >
> >so result should be
> >
> ><div> this is a div text </div>
> ><div> this is < div </div>
> >
> >because here div is also out of tags<....>
> >
> >but result should not be
> >
> ><div> this is a text </div>
> >
> >because here div is only in tags<...>
>
> Hi shahdharti,
>
> If you're on SQL Server 2005, consider using the xml datatype and an
> xquery expression.
>
> Otherwise, try if this works:
> SELECT data
> FROM #t
> WHERE REPLACE(REPLACE(data, '</div>', ''), '<div>', '') LIKE '%div%'
>
>
> --
> Hugo Kornelis, SQL Server MVP

This query also not works.

Actually the string will be most possible HTML Strings. Can I use
xquery for html strings?

24 Jun 2006 12:14 PM

Erland Sommarskog

(shahdha***@gmail.com) writes:

> Actually the string will be most possible HTML Strings. Can I use
> xquery for html strings?

Only if you can convert them to XML. Since XML is stricter than HTML
on closing tags etc, I'm skeptic that this will work out well.

A better on alternative on SQL 2005 would be to write a user-defined
function in C# or VB .Net that strips values of HTML tages and returns
the meat so you search on that text.

If you are on SQL 2000, you may be better of running it all client-side.

A joker here is full-text. I don't know full-text, but maybe it has some
built-in support for searching outside HTML tags.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

24 Jun 2006 10:03 PM

Hugo Kornelis

On 24 Jun 2006 04:05:22 -0700, shahdha***@gmail.com wrote:

>
>Hugo Kornelis wrote:
(snip)
>> Otherwise, try if this works:
>> SELECT data
>> FROM #t
>> WHERE REPLACE(REPLACE(data, '</div>', ''), '<div>', '') LIKE '%div%'
(snip)
>This query also not works.

Hi shahdharti,

Define: not works.

With the test data you supplied, the query produced the expected
results. So maybe there's some extra requirement that you forgot to
sppecify?

--
Hugo Kornelis, SQL Server MVP