count substrings - 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 2008 Forums

Transact-SQL (2008)

count substrings

Author

Topic

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2010-03-11 : 10:04:08

ok, this will be used in access and maybe sql server 2008 so i'm ideally looking for something that's pretty straightforward although it could be a lengthy statement if needed.

i have a table (tblPages) where i'm searching page content based on a number of terms. so my query might look like

select * from tblPages where pageContent like '%search term 1%' or pageContent like '%search term 2%' or pageContent like '%search term 3%'

is it possible to return something as follows
pageName / term1 occurence / term2 occurence / term3 occurence / pagecontent
page1.html / 4 / 0 / 2 / lorem ipsum, etc
page2.html / 2 / 0 / 1 / blah de blah, etc
page3.html / 0 / 2 / 1 / rah rah rah, etc

the select query above is created dynamically so i can add anything to it. how would i return the occurences of each search term as a new column which i could then use to order my results by? any ideas are welcome

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-11 : 16:29:04

You can calculate the number of occurrances of a substring by using the REPLACE function to replace the substring with an empty string and seing its impact on total length:

select (len(original) - len(replace(original, search, ''))) / len(search) as OccuranceCount

If your original string is 'abcdXYZefgXYZh' and your search string is 'XYZ' then:

length(original) = 14
length(search) = 3
len(replace(original, search, '')) = 8

(14 - 8) / 3 = 6 / 3 = 2

You may need to account for zero length search strings.

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2010-03-12 : 04:27:42

unfortunately although the logic is solid, i can't use REPLACE because the JET driver is crap and doesn't support replace out of access (the query is being called via a webpage)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-12 : 11:38:43

I am NOT an Access person so take this with a huge grain of salt BUT...

I brought up a copy of Access 2007 and was able to invoke the REPLACE function as part of my query. Once I had a basic query created, I switched to "SQL View" and was able to make the additional changes to the script.

HTH

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources