Dear all,
Please help me to do the char count in a query...

Example:

table-A
---------
Nric Name
---- ----
G5558114U ramanathan
G5697744G rammohan
-----------------------

I want to find no of 'a'-s in the field Name

Nric Name No of 'a's
---- ---- ----------
G5558114U ramanathan 4
G5697744G rammohan 2
-------------------------------------------
Thanks in advance,
Ram DSL
www.dslsoft.net

Ram DSL wrote:
<snip>
I'm quote certain this can't be done in a single SQL-statement.

Make while-loop and play with charindex and substring.


--
Kristian Damm Jensen damm (at) ofir (dot) dk
Life is what happens to you while you're busy making other plans. --
John Lennon

Kristian Damm Jensen wrote:

This should work in 12.5.0.3 and higher

select char_length(name) - char_length(str_replace(name,"a",""))

Unfortunately ASE puts a space instead of an empty string so the Name
field doesn't change length.

However str_replace(name,"aa","b") will shorten the string, so this
sounds like a bug to me.

Adam

reachram (AT) bharatmail (DOT) com (Ram DSL) wrote in message news:<f1565749.0402110310.748bc297 (AT) posting (DOT) google.com>...

This can be done in ASE 12.5.1 and higher using the str_replace()
function. If name is a varchar:

select Nric, Name, datalength(name) - datalength(str_replace(name,
"a", null)) as "No of 'a's" from table

If name is a char:

select Nric, Name, datalength(convert(varchar(255),name)) -
datalength(convert(varchar(255), str_replace(name, "a", null))
as "No of 'a's" from table

-bret

"Ram DSL" <reachram (AT) bharatmail (DOT) com> wrote


There are different ways of solving this problem without using a loop,
depending on the ASE version you're running.
For simplicity, I'm assuming your table is named 't' and the column is
named 'col'.

First, in 12.5.1 or later (note: this code runs also in 12.5.0.3, but
produces the wrong result):

declare @s varchar(10)
select @s = 'a'
select col,
(len(col) -
isnull(len(str_replace(col,@s,NULL)),0))/len(@s) "#occurrences"
from t

col #occurrences
--------------- ------------
ramanathan 4
rammohan 2

(2 rows affected)



In 12.5.0.3 or earlier, use this code instead:


set rowcount 99
select n=identity(2) into #t2 from syscolumns
set rowcount 0

declare @s varchar(10)
select @s = 'a'

select distinct col, count(*) "#occurrences"
from t, #t2
where substring(col,#t2.n,char_length(@s)) = @s
group by col


col #occurrences
--------------- ------------
ramanathan 4
rammohan 2

(2 rows affected)



HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of "Tips, Tricks & Recipes for Sybase ASE" and
"The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:rob (AT) YOUR (DOT) SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

Rob Verschoor wrote:
Rob,

I don't see why yours works and my version doesn't! (whinge whinge)

The help file states: "If any of the three arguments is NULL, the
function returns NULL."

This is why I chose to replace the character with the empty string.
Conceptually, and in every other language with the feature, replacing a
character with an empty string is perfectly valid and the language
should never decide to supplement a space character instead!

Adam

Probably because you ran it in 12.5.0.3. As I mentioned, the code with
str_replace() actually runs in 12.5.0.3, but the results are incorrect
for the problem we're trying to solve here. The reason it works in
12.5.1 is that the NULL argument doesn't render the result NULL (as it
does in 12.5.0.3) but actually removes the string from the source
string (much like the stuff() function does).
The comment in the 12.5.1 docs about the behaviour of NULL values
hasn't been updated correspondingly (yet).

As for the 'empty' string: there is no such concept in Transact-SQL. A
string of '' (two single quotes) always evaluates to a single space --
this is documented, it's not a bug. There are some workarounds: see
the bit about the 'empty string' in my ASE QuickRef guide.

Anyway, here's a workaround for 12.5.0.3: replace each string by a
string that's one character longer -- the total increase in length
corresponds to the number of occurrences. This has the risk of
overrunning the length of the string, which is why I don't like it.

HTH,

Rob V.

Rob Verschoor wrote:

I was running it in 12.5.1 - but I was being confused by the incorrect
documentation, and my assumption that an empty string would be empty.

Is this empty string issue, documented or not, a design choice by
Sybase? Part of ANSI? Something every RDBMS does but i've just not seen?

Adam

(I'd missed the bit in your guide about the empty string - it's tucked
down at the bottom of the page and wasn't obvious enough for me.
Especially before coffee time.)

"Adam H" <adam_NO_SPAMM_horan (AT) yahoo (DOT) com> wrote


It's a 'feature' of ASE, and it has always been like that (MS-SQL
behaved identically until 6.0 and still does when it's in 6.x
compatibilty mode). This behaviour not ANSI-compliant, and other DBMSs
have usually implemented something different that isn't ANSI-compliant
either (Oracle treats a string with a length of zero as NULL, for
example).

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of "Tips, Tricks & Recipes for Sybase ASE" and
"The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:rob (AT) YOUR (DOT) SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

Nope Its not working, It results Null. PL help me to find out the correct way.
ram DSL, London
www.dslsoft.net

"Rob Verschoor" <rob (AT) DO (DOT) NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote