?
Solved

SQL - Count keyword in string

Posted on 2014-10-10
4
Medium Priority
?
132 Views
Last Modified: 2014-10-15
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?
0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 40373959
I'm assuming the output for the sample data above would be

0
1
2
3
2

Is that correct?

if so, try this...

patindex('%[^#]%',yourstring)-1
0
 
LVL 3

Assisted Solution

by:santanu30in
santanu30in earned 1000 total points
ID: 40375791
hi

You can do  like below

DECLARE @sString VARCHAR(1000)
SET @sString = '##Testing###'

SELECT LEN(@sString) - LEN(REPLACE(@sString, '#', ''))

Or you can do like below

SELECT (LEN(ColumnName) - LEN(REPLACE(BILLNO, '#', ''))) AS sCount FROM TableName
0
 

Author Closing Comment

by:holemania
ID: 40381013
Thank you.  Both solution works equally well.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40382114
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

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

Open in new window

)
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question