How to count the number of substring occurances in a string

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.
parvezworldAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jdlambert1Connect With a Mentor Commented:
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
 
ChrisKingConnect With a Mentor Commented:
use the stuff() function to replace data, but this will only replace one occurance

as jdlambert1 said, you will need a loop
0
 
pitsnogleCommented:
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
 
nexusSamCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.