Searching a string... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Searching a string...

Author

Topic

Crespo

85 Posts

Posted - 2002-11-19 : 03:59:19

Is there an SQL function that would return the number of occurences of a particular character in a given string?

For example, say I have the sentance 'The cat sat on the mat'.

I want a function that would tell me how many times the letter 'a' occurs in the sentance.

Now I know how to do this within a loop etc using CHAINDEXs but I was hoping that there is a neat or better way of doing it.

Your help would be appreciated.

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-19 : 04:11:13

Could use recursion but would probably come up against the nest limit.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Crespo

85 Posts

Posted - 2002-11-19 : 04:19:03

quote:

Could use recursion but would probably come up against the nest limit.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Yes... and that limits the length of the string. You know... I am thinking if using a CUR... CURRRR... Cursor! What do you think?

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-19 : 04:34:20


SELECT LEN(catcol) - LEN(REPLACE(catcol, 'a', ''))
FROM (SELECT 'The cat sat on the mat' AS catcol) a

May need to use DATALENGTH depending on considerations about trailing spaces.

Crespo

85 Posts

Posted - 2002-11-19 : 04:43:20

quote:

SELECT LEN(catcol) - LEN(REPLACE(catcol, 'a', ''))
FROM (SELECT 'The cat sat on the mat' AS catcol) a

May need to use DATALENGTH depending on considerations about trailing spaces.

Welldone.... that is a very intersting way of doing it... I did not think of eliminating the As and subtracting the original length. Thanks!

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

Crespo

85 Posts

Posted - 2002-11-19 : 05:07:00

OK... maybe you can help with the following... I know it can be done in a much simpler way, but i am using a very strange and twsited logic.
Right he rs is what I am trying to do....

I have lines of addresses..

12 Dan-Yr-Heol, Cyncoed, Cardiff
Bakewell House, 32 High Street, Steeple Ashton, Wi
2 Bronte Grove, Gaer Estate, Newport
61 Preston Avenue, Newport, South Wales

Now I need to separate each address into separate parts, i.e. for the first line we would have THREE address parts
so bsically where ever a comma occurs we split the address.

This is what I have done so far...

SELECT ADDRESS,
LTRIM(SUBSTRING(ADDRESS, 1, CHARINDEX(',', ADDRESS)-1)),
CASE
WHEN CHARINDEX(',', ADDRESS, CHARINDEX(',', ADDRESS)+1)-CHARINDEX(',', ADDRESS)-1 > 0
THEN RTRIM(LTRIM(SUBSTRING(ADDRESS,CHARINDEX(',', ADDRESS)+1, CHARINDEX(',', ADDRESS,
CHARINDEX(',', ADDRESS)+1)-CHARINDEX(',', ADDRESS)-1)))
ELSE
''
END
FROM COGENT_ALL

By the way... COGENT_ALL is the table I am working on... but you only need to be concerned with one column called address.

Can you help ?

I could do it myself but I am interested in a different approach thats all!

Thanks.

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2002-11-19 : 05:54:35

Hey, this is my first solution :-)

You can do it with a loop and a temp table

create table #testtable(test varchar(250))

declare @sometext varchar(250)

set @sometext = 'a,b,c,d'

while len(rtrim(@sometext))>1

begin
insert into #testtable values (rtrim(LTRIM(SUBSTRING(@sometext, 1, CHARINDEX(',', @sometext)-1))))
set @sometext = substring(@sometext,CHARINDEX(',', @sometext)+1,250)
end

insert into #testtable values (rtrim(LTRIM(@sometext)))
go

select * from #testtable
go

drop table #testtable
go

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-19 : 07:15:56

There are some great articles on this site that deal with working with CSV strings.

For example...


drop table #test
create table #test (a varchar(30))
insert into #test
select 'followers,of,rambaldi' union
select 'from,the,eye,to,the,hand' union
select 'prophet,seer,psychic,alchemist'

declare @sep char(1)
select @sep = ','

select
    nullif(substring(@sep+a+@sep,n,charindex(@sep,@sep+a+@sep,n)-n),'') as element
from
    toolbox.dbo.numbers
    cross join #test
where
    n<=datalength(@sep+a+@sep) and
    n-datalength(@sep)>0 and
    substring(@sep+a+@sep,n-datalength(@sep),datalength(@sep))=@sep and
    charindex(@sep,@sep+a+@sep,n)-n>0

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources