fetching the rows that include the word btwn tags

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

fetching the rows that include the word btwn tags

Author

Topic

tanmuhittin
Starting Member

3 Posts

Posted - 2012-01-17 : 13:27:11

Please follow A the C instructions in the B e-mail that C has been sent A to xxx@gmail.com to C complete your registration.

hi guys
i have a table which have sentences in every row like the sentence above
i want a sql command which will return the rows that includes the word i am looking for
but i want sql to return this row if this word is between A C or B C tags

i have tried some codes for this but they did not work well

for example
$a=xxx@gmail.com
select text from table where text like '%A$aC%' or text like '%B$aC%'
this command can find 'the' which is between A and C tags but it can not find 'xxx@gmail.com'

then i tried this one
$a=xxx@gmail.com
select text from table where text like '%A%$a%C%' or text like '%B%$a%C%'
this command can find 'xxx@gmail.com' however when you tried 'instructions' the program fetches the row again because 'instructions' is between A and C (the C after 'that') but because the tag closes with C normally 'instructions' is not between tags.

please help me to solve this problem.

Being cool is easy but it takes some practice

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-17 : 13:35:03

This works. But, depending on your actual data and "tags," this is VERY fragile implementation:

DECLARE @Table TABLE (Val VARCHAR(4000))
INSERT @Table SELECT 'Please follow A the C instructions in the B e-mail that C has been sent A to xxx@gmail.com to C complete your registration.'

--DECLARE @A VARCHAR(4000) = 'xxx@gmail.com'
--DECLARE @A VARCHAR(4000) = 'the'
DECLARE @A VARCHAR(4000) = 'foo'

SELECT *
FROM @Table
WHERE 
    Val LIKE '%A%' + @A + '%C%'
    OR Val LIKE '%B%' + @A + '%C%'

tanmuhittin
Starting Member

3 Posts

Posted - 2012-01-17 : 14:55:59

first thank you for your interest
i have tried it but it did not fetch any result
to be clear i will give an example sentence from one of my rows

Migration has </w:t></w:r><w:ins w:id="0" w:author="muhammed" w:date="2012-01-13T07:35:00Z"><w:r w:rsidR="003B3E09"><w:rPr><w:sz w:val="24"/><w:szCs w:val="24"/></w:rPr><w:t xml:space="preserve">always </w:t></w:r></w:ins><w:r w:rsidRPr="009F5B82"><w:rPr><w:sz w:val="24"/><w:szCs w:val="24"/></w:rPr><w:t xml:space="preserve">been one of the fundamental problems of modern governments due to innovations in transportation and indeed, industrialization.

these tags are xml tags and i want to fetch this sentence(row) if the word which is searched is between "<w:t xml:space="preserve">" and "</w:t></w:r></w:ins>" tags or it is between "<w:t>" and "</w:t></w:r></w:ins>" tags

Being cool is easy but it takes some practice

tanmuhittin
Starting Member

3 Posts

Posted - 2012-01-17 : 15:20:38

also i would like to add that there may be more than one word between these tags and i would like to retrieve this row is one of the words between these tags is searched

Being cool is easy but it takes some practice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources