Solved

SQL - Count keyword in string

Posted on 2014-10-10
4
126 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 250 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 250 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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