Solved

How to count the number of substring occurances in a string

Posted on 2004-08-21
6
597 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
6 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 250 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 250 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now