Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to count the number of substring occurances in a string

Posted on 2004-08-21
6
Medium Priority
?
605 Views
Last Modified: 2008-02-01
I am using ASE 11.x. I need to write a SQL statement that will return a count of substring occurances in a varchar column.

I tried doing something like this

select char_length('a.b.c') - char_length(replace('a.b.c', '.', ' ')) but looks like ASE 11.x does not support replace function.

How do I get, for example, a count of '.' in string, 'a.b.c' using ASE 11.x T-SQL?

Thanks.
0
Comment
Question by:parvezworld
[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
6 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 1000 total points
ID: 11865344
There's not a built-in function for this. You'll have to create a stored procedure with a loop, and custom function, or an extended stored procedure.
0
 
LVL 6

Assisted Solution

by:ChrisKing
ChrisKing earned 1000 total points
ID: 11865795
use the stuff() function to replace data, but this will only replace one occurance

as jdlambert1 said, you will need a loop
0
 

Expert Comment

by:pitsnogle
ID: 11880471
Since I don't have an ASE 11.x server to test, not sure the syntax is correct. Also, this will work with 11.5, not sure what version you have.

declare @new varchar(255)
declare @count int
declare @marker int

select @new="a.b.c"
select @count=0
select @marker=0

while (@new)!="c"
begin
@marker=patindex("%.%",@new)
@new=substring(@new,@marker,6)
select @count=@count+1
end

select @count


Give it a try and if this don't work, you get the idea and what functions you can use to do the loop and store the count.

good luck,
Rich
0
 
LVL 2

Expert Comment

by:nexusSam
ID: 11893613
parvezworld, do you mean how many records have the given substring in them or how many times within each record within the table that substring appears?
If the former, this is a very simple query:
SELECT COUNT(*) as NumFound FROM <tablename> WHERE CHARINDEX("<pattern>", <textcolumn>) != 0
-- Values in <> brackets should be replaced by whatever they're called in your database

HTH
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The main intent of this article is to make you aware of ‘Exchange fail to mount’ error, its effects, causes, and solution.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 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