mysql split string by comma and count
SELECT (LENGTH(`table`.`fieldname`) - LENGTH( REPLACE( `table`.`fieldname` , ',', '' ) ) + 1) AS `number` FROM `table` WHERE TRIM( IFNULL( `table`.`fieldname` , '' ) ) > ''
I had an issue while migrating (My)SQL data back and forth from Linux and Windows. When ever I made come changes on my local windows dev system and tried to apply the changes to the production system the SQL statement failed. Later I noticed that the table names on Windows were no matter what I tried in lower case. The MariaDB docs told me that the table names on Windows where always lower case and case insensitive. What a bummer.
Searching I found
lower_case_table_names in the docs. With the values 0,1 and 2.
0 (Unix) = table names and aliases and database names are compared in a case-sensitive manner.
1 (Windows) = names are stored in lowercase and not compared in a case-sensitive manner.
2 (Mac OS X) = names are stored as declared, but compared in lowercase.
I tried 0 and got
[ERROR] The server option 'lower_case_table_names' is configured to use case sensitive table names but the data directory resides on a case-insensitive file system. Please use a case sensitive file system for your data directory or switch to a case-insensitive table name mode. [ERROR] Aborting
But setting it to 2 works. No longer problems
[mysqld] datadir=C:/Program Files/MariaDB 10.2/data port=3306 innodb_buffer_pool_size=1003M character-set-server=utf8 performance_schema = ON lower_case_table_names = 2 [client] port=3306 plugin-dir=C:/Program Files/MariaDB 10.2/lib/plugin
I wonder cause in the old days of using MySQL it never was a problem.