brokeMyLegBiking
asked on
How do I find out the number of times that vbCrLf appears in a string
I have a string and I want to count the number of times that a certain character appears in that string.
The particular character that I want to find is vbCrLf
thx,
-broke
The particular character that I want to find is vbCrLf
thx,
-broke
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops fogot to change the replace expression
change above code like this
declare @mystring varchar(100)
set @mystring = 'Perhaps it will pass' + char(13) + char(10) + 'second Line'
select (len(@mystring) - len(replace(@mystring, char(13) + char(10), ''))) /2 AS [Occurences]
change above code like this
declare @mystring varchar(100)
set @mystring = 'Perhaps it will pass' + char(13) + char(10) + 'second Line'
select (len(@mystring) - len(replace(@mystring, char(13) + char(10), ''))) /2 AS [Occurences]
>>select (len(@mystring) - len(replace(@mystring, 'p', ''))) AS [Occurences]
in general you can't do this with Len since it ignores trailing spaces...
you need to use Datalength instead
so that 'xxxxxxx CRLF' would result in 1 rather than 2..
....
the other problem with appari's code is of course that singleton char(13) and/or char(10) occurennces will also be included in the
eventual count..
your best bet is probably to acutally use a while loop and navigate and count the occurrences...
e.g. something like
declare @mystring varchar(100)
set @mystring = 'Perhaps it will pass' + char(13) + char(10) + 'second Line'
declare @pos int,@mlength int
set @mlth=datalength(@mystring )
set @pos=1
declare @k int
While @pos <= @mlth
begin
@pos=charindex(char(13)+ch ar(10),@my string,@po s)
if @pos > 0 set @k=@k+1
if @pos > 0 set @pos=@pos+2
end
select k AS [Occurences]
in general you can't do this with Len since it ignores trailing spaces...
you need to use Datalength instead
so that 'xxxxxxx CRLF' would result in 1 rather than 2..
....
the other problem with appari's code is of course that singleton char(13) and/or char(10) occurennces will also be included in the
eventual count..
your best bet is probably to acutally use a while loop and navigate and count the occurrences...
e.g. something like
declare @mystring varchar(100)
set @mystring = 'Perhaps it will pass' + char(13) + char(10) + 'second Line'
declare @pos int,@mlength int
set @mlth=datalength(@mystring
set @pos=1
declare @k int
While @pos <= @mlth
begin
@pos=charindex(char(13)+ch
if @pos > 0 set @k=@k+1
if @pos > 0 set @pos=@pos+2
end
select k AS [Occurences]
>> the other problem with appari's code is of course that singleton char(13) and/or char(10) occurennces will also be included in the eventual count.. <<
I have to disagree with this. As long the replace search argument is *both* CHAR(13) + CHAR(10), you should only find combinations, not single occurences.
I have to disagree with this. As long the replace search argument is *both* CHAR(13) + CHAR(10), you should only find combinations, not single occurences.
>> the other problem with appari's code is of course that singleton char(13) and/or char(10) occurennces will also be included in the eventual count.. <<
thanks scott , had another look and i agree on that one....
for some reason i thought it was replace(replace(column,cha r(13),''), char(10),' ')
but the embedded space problem still arises!
thanks scott , had another look and i agree on that one....
for some reason i thought it was replace(replace(column,cha
but the embedded space problem still arises!
Yep, you're quite right about that.
vbcrlf converts to char(13) + char(10)
use above logic and search for char(13) and char(10).
change above code like this
declare @mystring varchar(100)
set @mystring = 'Perhaps it will pass' + char(13) + char(10) + 'second Line'
select (len(@mystring) - len(replace(@mystring, 'p', ''))) /2 AS [Occurences]