Hi
I need to cound the number of CHAR(11) charactors in a string.
I am currently attempting to use: len(string) - len(replace(string,
CHAR(11), ''))
But it seems to return far too many
Any help would be much appreciated
Thanks
B> But it seems to return far too many
Can you give an example?
SELECT LEN('foo') - REPLACE(LEN('foo'), CHAR(11), '')
returns 0...|||select dbo.OCCURS2 (string, CHAR(11))
CREATE function OCCURS2 (@.cSearchExpression nvarchar(4000),
@.cExpressionSearched nvarchar(4000))
returns smallint
as
begin
return
case
when datalength(@.cSearchExpression) > 0
then ( datalength(@.cExpressionSearched)
- datalength(replace(cast(@.cExpressionSear
ched as
nvarchar(4000)) COLLATE Latin1_General_BIN,
cast(@.cSearchExpression
as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
/ datalength(@.cSearchExpression)
else 0
end
end
GO
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wcon...e~2,54,33,27115
Please, download the file
http://www.universalthread.com/wcon...treme~2,2,27115
With the best regards,
Igor.
"Ben" wrote:
> Hi
> I need to cound the number of CHAR(11) charactors in a string.
> I am currently attempting to use: len(string) - len(replace(string,
> CHAR(11), ''))
> But it seems to return far too many
> Any help would be much appreciated
> Thanks
> B
>
>|||hi
just try this:
its same as ur implementation:
declare
@.ch varchar(10)
set @.ch = 'ABC' + char(11) + 'DEF'
select len(@.ch) - len (replace(@.ch,char(11),''))
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Ben" wrote:
> Hi
> I need to cound the number of CHAR(11) charactors in a string.
> I am currently attempting to use: len(string) - len(replace(string,
> CHAR(11), ''))
> But it seems to return far too many
> Any help would be much appreciated
> Thanks
> B
>
>|||Chandra,
Please correct me if I am wrong but your solution may only work for one
occurrence of char(11).
I tried the following and still got 1 instead of 2.
declare
@.ch varchar(10)
set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
select len(@.ch) - len (replace(@.ch,char(11),''))
http://zulfiqar.typepad.com
BSEE, MCP
"Chandra" wrote:
> hi
> just try this:
> its same as ur implementation:
> declare
> @.ch varchar(10)
> set @.ch = 'ABC' + char(11) + 'DEF'
> select len(@.ch) - len (replace(@.ch,char(11),''))
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Ben" wrote:
>|||Zulfiqar,
You've declared @.ch so that it can only hold 10 characters.
So the value you assigned to @.ch is truncated to
'ABC'+ CHAR(11) + 'DEFjh'
and this does in fact have only one char(11) value.
If you change the varchar(10) declaration to varchar(20) or larger,
you will get the result 2.
Steve Kass
Drew University
ZULFIQAR SYED wrote:
>Chandra,
>Please correct me if I am wrong but your solution may only work for one
>occurrence of char(11).
>I tried the following and still got 1 instead of 2.
>declare
>@.ch varchar(10)
>set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
>select len(@.ch) - len (replace(@.ch,char(11),''))
>
>|||Try:
declare
@.ch varchar(20)
set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
select '*' + @.ch + '*', len(@.ch), len (replace(@.ch,char(11),''))
Since @.ch was varchar(10):
ABCEFjhi
123456790
The char(11) fell off of the end.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ZULFIQAR SYED" <DRSQLnospam2005@.hotmail.com> wrote in message
news:C0F8C537-9633-4880-831F-1497766F6870@.microsoft.com...
> Chandra,
> Please correct me if I am wrong but your solution may only work for one
> occurrence of char(11).
> I tried the following and still got 1 instead of 2.
> declare
> @.ch varchar(10)
> set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
> select len(@.ch) - len (replace(@.ch,char(11),''))
> --
> http://zulfiqar.typepad.com
> BSEE, MCP
>
> "Chandra" wrote:
>|||Sounds like another case for repeating the "string or binary data would be
truncated" error on invalid variable assignments. I wonder how often this
happens in the real world and people have no idea they're losing data.
"Steve Kass" <skass@.drew.edu> wrote in message
news:u$k1WGfqFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Zulfiqar,
> You've declared @.ch so that it can only hold 10 characters.
> So the value you assigned to @.ch is truncated to
> 'ABC'+ CHAR(11) + 'DEFjh'
> and this does in fact have only one char(11) value.
> If you change the varchar(10) declaration to varchar(20) or larger,
> you will get the result 2.
> Steve Kass
> Drew University
> ZULFIQAR SYED wrote:
>|||Thank you everyone
Igor2004's solution worked perfectly.
Thanks again
B
"Igor2004" <Igor2004@.discussions.microsoft.com> wrote in message
news:D82A4BEE-4EC9-44DF-BD41-1A0E3D061EC3@.microsoft.com...
> select dbo.OCCURS2 (string, CHAR(11))
> CREATE function OCCURS2 (@.cSearchExpression nvarchar(4000),
> @.cExpressionSearched nvarchar(4000))
> returns smallint
> as
> begin
> return
> case
> when datalength(@.cSearchExpression) > 0
> then ( datalength(@.cExpressionSearched)
> - datalength(replace(cast(@.cExpressionSear
ched as
> nvarchar(4000)) COLLATE Latin1_General_BIN,
> cast(@.cSearchExpression
> as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
> / datalength(@.cSearchExpression)
> else 0
> end
> end
> GO
> For more information about string UDFs Transact-SQL please visit the
> http://www.universalthread.com/wcon...e~2,54,33,27115
> Please, download the file
> http://www.universalthread.com/wcon...treme~2,2,27115
> With the best regards,
> Igor.
>
> "Ben" wrote:
>
