|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Count CHAR(11) in a stringHi
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(@cExpressionSearched 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/wconnect/wc.dll?LevelExtreme~2,54,33,27115 Please, download the file http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115 With the best regards, Igor. Show quote "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 > > > Thank you everyone
Igor2004's solution worked perfectly. Thanks again B Show quote "Igor2004" <Igor2***@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(@cExpressionSearched 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/wconnect/wc.dll?LevelExtreme~2,54,33,27115 > > Please, download the file > http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~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),'')) -- Show quotebest 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),'')) Show quote "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: > > > 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 > > > > > > 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: Show quote >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),'')) > > > 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. Show quote "Steve Kass" <sk***@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: > >>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): ABC½EFjhi 123456790 The char(11) fell off of the end. -- Show quote---------------------------------------------------------------------------- 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: > >> 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 >> > >> > >> >  |
|||||||||||||||||||||||
Other interesting topics