parvezworld
asked on
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.
I tried doing something like this
select char_length('a.b.c') - char_length(replace('a.b.c
How do I get, for example, a count of '.' in string, 'a.b.c' using ASE 11.x T-SQL?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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("%.%",@ne
@new=substring(@new,@marke
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