Counting chars in SQL.

Steve Grimes

Counting chars in SQL.
Hi, z/OS, DB2 V7.1 here,

Any nifty techniques for returning the number (count) of specific
characters (say, commas) in a column using SQL?

Thanks.

Stg

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Kintisch

Re: Counting chars in SQL
(in response to Steve Grimes)
Hi Steve,
A few thoughts. If you really need this on an adhoc query basis,
write a UDF [User Defined Function] of the form
COUNTCHAR(source-string, search-string) that returns an integer. Use
the "POSSTR" scalar function as a model. You may need a few of the
functions, to operate on a CHAR or a VARCHAR. They can be written in
any of a few languages.
If all that you need to know is "Is there at least 1 Comma in ..."
then just use POSSTR and test for zero or non-zero result [if not null].
If on the other hand you have a specific need [like knowing that
in "this address column" there are "N" commas] then I'd add a column
"ADDR_COMMAS" to the table and add triggers for insert into the table
or update of the column that loop through the column text using
"POSSTR" and "SUBSTR", count the commas and update the "ADDR_COMMAS"
column. Then searching on the "ADDR_COMMAS" column would be very
fast and indexable.

Larry Kintisch ABLE Information Services www.DBIndexDesign.com

At 04:27 PM 1/5/2007, you wrote:

>Hi, z/OS, DB2 V7.1 here,
>
>Any nifty techniques for returning the number (count) of specific
>characters (say, commas) in a column using SQL?
>
>Thanks.
>
>Stg
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
>and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
>that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG
>conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John Miller

Re: Counting chars in SQL.
(in response to Larry Kintisch)
select length('A,B,C,D') - length(replace('A,B,C,D', ',', '')) from
sysibm.sysdummy1



:-)



John



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Steve Grimes
Sent: Friday, January 05, 2007 2:28 PM
To: [login to unmask email]
Subject: [DB2-L] Counting chars in SQL.




Hi, z/OS, DB2 V7.1 here,

Any nifty techniques for returning the number (count) of specific
characters (say, commas) in a column using SQL?

Thanks.

Stg
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steve Grimes

Re: Counting chars in SQL.
(in response to John Miller)
Thanks Larry & John! John's trick did the trick!
Stg

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

GANESH GOSAVI

Re: Counting chars in SQL.
(in response to Steve Grimes)
Writing an UDF in C language and register it with your DB would be the best option. It will allow you more generalization.

Thanks and Regards,

GANESH GOSAVI, IBM Pune, India Labs


----- Original Message ----
From: John Miller <[login to unmask email]>
To: [login to unmask email]
Sent: Saturday, 6 January, 2007 3:49:25 AM
Subject: Re: [DB2-L] Counting chars in SQL.


select length('A,B,C,D') - length(replace('A,B,C,D', ',', '')) from sysibm.sysdummy1

J

John




From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Steve Grimes
Sent: Friday, January 05, 2007 2:28 PM
To: [login to unmask email]
Subject: [DB2-L] Counting chars in SQL.


Hi, z/OS, DB2 V7.1 here,

Any nifty techniques for returning the number (count) of specific characters (say, commas) in a column using SQL?

Thanks.

Stg
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Send free SMS to your Friends on Mobile from your Yahoo! Messenger. Download Now! http://messenger.yahoo.com/download.php

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: Counting chars in SQL.
(in response to GANESH GOSAVI)
I can only presume you've never looked at the overheads of a UDF - my experience is that
executing a UDF more than doubles the response time for each row. .

If you want to go down the UDF route, writing an SQL scalar function using John's code as a
basis would be better - at least you won't get the task switching to a WLM address space.

James Campbell


On 5 Jan 2007 at 19:48, Ganesh Gosavi wrote:

>
> Writing an UDF in C language and register it with your DB would be the best option. It will allow
> you more generalization.
>
> Thanks and Regards,
>
> GANESH GOSAVI, IBM Pune, India Labs
>
> ----- Original Message ----
> From: John Miller <[login to unmask email]>
> To: [login to unmask email]
> Sent: Saturday, 6 January, 2007 3:49:25 AM
> Subject: Re: [DB2-L] Counting chars in SQL.
>
> select length('A,B,C,D') - length(replace('A,B,C,D', ',', '')) from
> sysibm.sysdummy1
>
> J
>
> John
>
>
>
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Steve
> Grimes
> Sent: Friday, January 05, 2007 2:28 PM
> To: [login to unmask email]
> Subject: [DB2-L] Counting chars in SQL.
>
>
>
> Hi, z/OS, DB2 V7.1 here,
>
> Any nifty techniques for returning the number (count) of specific characters (say, commas) in a
> column using SQL?
>
> Thanks.
>
> Stg

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Haldur Johansen

Re: Counting chars in SQL.
(in response to James Campbell)
John's solution is true:
select length('A,B,C,D') - length(replace('A,B,C,D', ',', '')) from
sysibm.sysdummy1

give us the result of 3 commas.

But what if for instance:

select length('AAAA,B,C,D') - length(replace('A,B,C,D', ',', '')) from
sysibm.sysdummy1

Here the result is 6, but there are only 3 commas!

Regards,

Haldur Johansen, Systems Architect
Elektron, Staravegur 9, P.O. BOX 219, FO-110 Tórshavn





"John Miller"
<[login to unmask email]
COITEK.COM> To
Sent by: "DB2 <[login to unmask email]>
Data Base cc
Discussion List"
<[login to unmask email] Subject
ORG> Re: [DB2-L] Counting chars in SQL.


01/05/2007 10:19
PM


Please respond to
"DB2 Database
Discussion list
at IDUG"
<[login to unmask email]
2-L.ORG>







select length('A,B,C,D') - length(replace('A,B,C,D', ',', '')) from
sysibm.sysdummy1



J



John





From:DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Steve Grimes
Sent: Friday, January 05, 2007 2:28 PM
To: [login to unmask email]
Subject: [DB2-L] Counting chars in SQL.




Hi, z/OS, DB2 V7.1 here,

Any nifty techniques for returning the number (count) of specific
characters (say, commas) in a column using SQL?

Thanks.

Stg
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm




The information transmitted is intended only for the addressee and may
contain confidential, proprietary and/or privileged material. Any
unauthorized review, distribution or other use of or the taking of any
action in reliance upon this information is prohibited. If you receive this
in error, please contact the sender and delete or destroy this message and
any copies.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

dusan pospisil

Re: Counting chars in SQL.
(in response to Haldur Johansen)
It is necessary to repeate the same string after replace;
SELECT LENGTH('AAAA,B,C,D')
- LENGTH(REPLACE('AAAA,B,C,D', ',', '')) FROM
SYSIBM.SYSDUMMY1 ;
3
Regards,
dp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Haldur Johansen
Sent: Monday, January 08, 2007 1:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Counting chars in SQL.

John's solution is true:
select length('A,B,C,D') - length(replace('A,B,C,D', ',', '')) from
sysibm.sysdummy1

give us the result of 3 commas.

But what if for instance:

select length('AAAA,B,C,D') - length(replace('A,B,C,D', ',', '')) from
sysibm.sysdummy1

Here the result is 6, but there are only 3 commas!

Regards,

Haldur Johansen, Systems Architect
Elektron, Staravegur 9, P.O. BOX 219, FO-110 Tórshavn





"John Miller"
<[login to unmask email]
COITEK.COM> To
Sent by: "DB2 <[login to unmask email]>
Data Base cc
Discussion List"
<[login to unmask email] Subject
ORG> Re: [DB2-L] Counting chars in SQL.


01/05/2007 10:19
PM


Please respond to
"DB2 Database
Discussion list
at IDUG"
<[login to unmask email]
2-L.ORG>







select length('A,B,C,D') - length(replace('A,B,C,D', ',', '')) from
sysibm.sysdummy1



J



John





From:DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Steve Grimes
Sent: Friday, January 05, 2007 2:28 PM
To: [login to unmask email]
Subject: [DB2-L] Counting chars in SQL.




Hi, z/OS, DB2 V7.1 here,

Any nifty techniques for returning the number (count) of specific characters (say, commas) in a column using SQL?

Thanks.

Stg
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Shyam Peri

Re: Counting chars in SQL.
(in response to dusan pospisil)
On 1/8/07, Haldur Johansen <[login to unmask email]> wrote:
>
> John's solution is true:
> select length('A,B,C,D') - length(replace('A,B,C,D', ',', '')) from
> sysibm.sysdummy1
>
> give us the result of 3 commas.
>
> But what if for instance:
>
> select length('AAAA,B,C,D') - length(replace('A,B,C,D', ',', '')) from
> sysibm.sysdummy1



The idea behind John's solution looks something like the following:
select length(<char*>) - length(replace(<char*>, ',', '')) from
sysibm.sysdummy1
for example:
select length('AAAA,B,C,D') - length(replace('AAAA,B,C,D', ',', '')) from
sysibm.sysdummy1 (in the mentioned case)


Here the result is 6, but there are only 3 commas!
>
> Regards,
>
> Haldur Johansen, Systems Architect
> Elektron, Staravegur 9, P.O. BOX 219, FO-110 Tórshavn
>
>
>
>
>
> "John Miller"
> <[login to unmask email]
> COITEK.COM> To
> Sent by: "DB2 <[login to unmask email]>
> Data Base cc
> Discussion List"
> <[login to unmask email] Subject
> ORG> Re: [DB2-L] Counting chars in SQL.
>
>
> 01/05/2007 10:19
> PM
>
>
> Please respond to
> "DB2 Database
> Discussion list
> at IDUG"
> <[login to unmask email]
> 2-L.ORG>
>
>
>
>
>
>
>
> select length('A,B,C,D') - length(replace('A,B,C,D', ',', '')) from
> sysibm.sysdummy1
>
>
>
> J
>
>
>
> John
>
>
>
>
>
> From:DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
> Of Steve Grimes
> Sent: Friday, January 05, 2007 2:28 PM
> To: [login to unmask email]
> Subject: [DB2-L] Counting chars in SQL.
>
>
>
>
> Hi, z/OS, DB2 V7.1 here,
>
> Any nifty techniques for returning the number (count) of specific
> characters (say, commas) in a column using SQL?
>
> Thanks.
>
> Stg
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at
> http://conferences.idug.org/index.cfm
>
>
>
>
> The information transmitted is intended only for the addressee and may
> contain confidential, proprietary and/or privileged material. Any
> unauthorized review, distribution or other use of or the taking of any
> action in reliance upon this information is prohibited. If you receive
> this
> in error, please contact the sender and delete or destroy this message and
> any copies.
>
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm
>



--
warm regards
Peri

The best way out of difficulty is through it. :)

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm