holemania
asked on
SQL - Count keyword in string
Trying to google this, but can't find it anywhere. In my string, I have a set of symbols and I would like to count how many times it repeats.
Example the field is description. The description field would have the following:
Testing
#Testing
##Testing
###Testing
##Testing###
If the # shows once, I want it to count once. If it shows twice, it'll count twice. It's always from left to right with the special symbol. How could I count if there's 0 # for the first line, 1 count for the 2nd line etc? The last example, if it's not consecutive, I don't want to count the rest of the repeating "Keyword" or symbol. Is this even possible?
Example the field is description. The description field would have the following:
Testing
#Testing
##Testing
###Testing
##Testing###
If the # shows once, I want it to count once. If it shows twice, it'll count twice. It's always from left to right with the special symbol. How could I count if there's 0 # for the first line, 1 count for the 2nd line etc? The last example, if it's not consecutive, I don't want to count the rest of the repeating "Keyword" or symbol. Is this even possible?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually they don't both work equally well.
The second answer doesn't work.
It returns the total number of # in the string, not the length of the first consecutive set
The second answer doesn't work.
It returns the total number of # in the string, not the length of the first consecutive set
select patindex('%[^#]%',str)-1 sdstuber,
LEN(str) - LEN(REPLACE(str, '#', '')) santanu30in
from
(select '##Test String###' str
union all
select '#a#b#c#d#e' str
) x
sdstuber santanu30in
----------- -----------
2 5
1 5
(2 row(s) affected
)
ASKER