How to split comma separated string into rows in mysql?

-
When we have a comma-separated string like 1,2,3... and if we need to display as row like 

ID 
1 
2 
3 

Here is the query we can use for the same 

e.g. 

Table name: tblTest 

Column Name : vals 


SELECT 
     DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1) val 
FROM 
     tblTest 
     INNER JOIN (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit; 


Hope this is helpful!

Comments

Post a Comment

Popular posts from this blog

Error: The client and server cannot communicate, because they do not possess a common algorithm.

-
Error:MySql.Data.Common.Ssl.StartSSL(Stream& baseStream, Encoding encoding, String connectionString) at MySql.Data.MySqlClient.NativeDriver.Open() at MySql.Data.MySqlClient.Driver.Open() at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() at MySql.Data.MySqlClient.MySqlPool.GetConnection() at MySql.Data.MySqlClient.MySqlConnection.Open() This issue is related to a connection issue between a .NET application and a MySQL database. The stack trace shows the application encountering an exception while attempting to open a connection to the MySQL database. It seems like the error is due to a problem with establishing a Secure Sockets Layer (SSL) connection. The exception is thrown in the MySql.Data.Common.Ssl.StartSSL method, which suggests that the error occurred...
Read more

Disable Copy Paste And Mouse Right Click JavaScript, jQuery

-
Here is the code we can use to Disable Right Mouse Click Using jQuery: <script type="text/javascript"> $(document).ready(function () {     //Disable full page     $("body").on("contextmenu",function(e){         return false;     });           //Disable part of page     $("#id").on("contextmenu",function(e){         return false;     }); }); </script> Disable Cut, Copy, Paste Using jQuery: <script type="text/javascript"> $(document).ready(function () {     //Disable full page     $('body').bind('cut copy paste', function (e) {         e.preventDefault();     });           //Disable part of page     $('#id').bind('cut copy paste', function (e) {         e.preventDefault();     }); }); </script> Hope this will help!
Read more