We have numerous identical tables with a varchar column that holds data
like this: "0 0 0 1 0 1 0 25 7 0 139 0 9". Essentially there are a bunch
of integers with a single space as a separator. There _should_ be no more
than 30 entries ( and 29 spaces ), but sometimes the system misfires and
there are more or less. Is there a MySQL solution to getting a count of
the spaces present in the field, figuring that spaces + 1 will equal
entries? It's fairly straight forward using a PHP application, but I'd
like to get the DB server to accomplish this task. Not having much luck
finding a solution in the manual.

Thanks,

David

Hi,

dpgirago@mdanderson.org wrote:
> We have numerous identical tables with a varchar column that holds data
> like this: "0 0 0 1 0 1 0 25 7 0 139 0 9". Essentially there are a bunch
> of integers with a single space as a separator. There _should_ be no more
> than 30 entries ( and 29 spaces ), but sometimes the system misfires and
> there are more or less. Is there a MySQL solution to getting a count of
> the spaces present in the field, figuring that spaces + 1 will equal
> entries? It's fairly straight forward using a PHP application, but I'd
> like to get the DB server to accomplish this task. Not having much luck
> finding a solution in the manual.

You can find the number of spaces like this:

select length(col) - length(replace(col, ' ', ''))

Baron

dpgirago@mdanderson.org wrote:
> We have numerous identical tables with a varchar column that holds data
> like this: "0 0 0 1 0 1 0 25 7 0 139 0 9". Essentially there are a bunch
> of integers with a single space as a separator. There _should_ be no more
> than 30 entries ( and 29 spaces ), but sometimes the system misfires and
> there are more or less. Is there a MySQL solution to getting a count of
> the spaces present in the field, figuring that spaces + 1 will equal
> entries? It's fairly straight forward using a PHP application, but I'd
> like to get the DB server to accomplish this task. Not having much luck
> finding a solution in the manual.

SELECT
CHAR_LENGTH(field_name) - CHAR_LENGTH(REPLACE(field_name, ' ', '')) as
num_spaces FROM my_table;

Cheers,

Jay

Try this:

mysql> SELECT LENGTH('0 0 0 1 0 1 0 25 7 0 139 0 9') - LENGTH(REPLACE('0 0 0 1 0 1 0 25 7 0 139 0 9',' ','')) + 1;
+-----------------------------------------------------------------------------------------------------+
| LENGTH('0 0 0 1 0 1 0 25 7 0 139 0 9') - LENGTH(REPLACE('0 0 0 1 0 1 0 25 7 0 139 0 9',' ','')) + 1 |
+-----------------------------------------------------------------------------------------------------+
| 13 |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

or Try it like this:

mysql> SELECT LENGTH(st) - LENGTH(REPLACE(st,' ','')) + 1 FieldCount FROM (SELECT '0 0 0 1 0 1 0 25 7 0 139 0 9' st) A;
+------------+
| FieldCount |
+------------+
| 13 |
+------------+
1 row in set (0.00 sec)

----- Original Message -----
From: dpgirago@mdanderson.org
To: mysql@lists.mysql.com
Sent: Tuesday, September 4, 2007 11:36:41 AM (GMT-0500) America/New_York
Subject: finding count of spaces in a string

We have numerous identical tables with a varchar column that holds data
like this: "0 0 0 1 0 1 0 25 7 0 139 0 9". Essentially there are a bunch
of integers with a single space as a separator. There _should_ be no more
than 30 entries ( and 29 spaces ), but sometimes the system misfires and
there are more or less. Is there a MySQL solution to getting a count of
the spaces present in the field, figuring that spaces + 1 will equal
entries? It's fairly straight forward using a PHP application, but I'd
like to get the DB server to accomplish this task. Not having much luck
finding a solution in the manual.

Thanks,

David

--
Rolando A. Edwards
MySQL DBA

SWMX, Inc.
1 Bridge Street
Irvington, NY 10533
(914) 406-8406 (Main)
(201) 660-3221 (Mobile)

dpgirago@mdanderson.org ha scritto:
> We have numerous identical tables with a varchar column that holds data
> like this: "0 0 0 1 0 1 0 25 7 0 139 0 9". Essentially there are a bunch
> of integers with a single space as a separator. There _should_ be no more
> than 30 entries ( and 29 spaces ), but sometimes the system misfires and
> there are more or less. Is there a MySQL solution to getting a count of
> the spaces present in the field, figuring that spaces + 1 will equal
> entries? It's fairly straight forward using a PHP application, but I'd
> like to get the DB server to accomplish this task. Not having much luck
> finding a solution in the manual.
>
>

SELECT 1
+ CHAR_LENGTH("0 0 0 1 0 1 0 25 7 0 139 0 9")
- CHAR_LENGTH(REPLACE("0 0 0 1 0 1 0 25 7 0 139 0 9", " ", ""))
AS ret


HopeIt,
Francesco