REGEX in MySQL to display only numbers separated by hyphen.

MySQLMySQLi Database

<p style="">Let us first create a table &minus;</p><pre class="result notranslate" style="">mysql&gt; create table DemoTable &nbsp; &nbsp;( &nbsp; &nbsp; &nbsp; Code varchar(100) &nbsp; &nbsp;); Query OK, 0 rows affected (1.16 sec)</pre><p>Insert some records in the table using insert command &minus;</p><pre class="result notranslate">mysql&gt; insert into DemoTable values(&#39;100-677-9876&#39;); Query OK, 1 row affected (0.21 sec) mysql&gt; insert into DemoTable values(&#39;100-677-9876-John&#39;); Query OK, 1 row affected (0.16 sec) mysql&gt; insert into DemoTable values(&#39;David-100-677-9876&#39;); Query OK, 1 row affected (0.16 sec)</pre><p>Display all records from the table using select statement &minus;</p><pre class="prettyprint notranslate">mysql&gt; select *from DemoTable;</pre><p>This will produce the following output &minus;</p><pre class="result notranslate" style="">+--------------------+ | Code &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | +--------------------+ | 100-677-9876 &nbsp; &nbsp; &nbsp; | | 100-677-9876-John | | David-100-677-9876 | +--------------------+ 3 rows in set (0.00 sec)</pre><p>Following is the Regex to display only numbers separated by hyphen &minus;</p><pre class="prettyprint notranslate">mysql&gt; select *from DemoTable where Code REGEXP &#39;^\\(?[0-9]{3}\\)?[\\s-]?[0-9]{3}[\\s-]?[0-9]{4}$&#39;;</pre><p>This will produce the following output &minus;</p><pre class="result notranslate" style="">+--------------+ | Code &nbsp; &nbsp; &nbsp; | +--------------+ | 100-677-9876 | +--------------+ 1 row in set (0.00 sec)</pre>
raja
Updated on 22-Aug-2019 07:26:32

Advertisements