Link to home
Start Free TrialLog in
Avatar of brokeMyLegBiking
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
ASKER CERTIFIED SOLUTION
Avatar of ibost
ibost

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

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]
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]
Avatar of Lowfatspread
>>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)+char(10),@mystring,@pos)
      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.
>> 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,char(13),''),char(10),'')

but the embedded space problem still arises!
Yep, you're quite right about that.