Fetch rows where a field value is less than 5 chars in MySQL?

MySQLMySQLi Database

<p>To fetch rows where a field value is less than 5 chars, you need to use LENGTH() function. The syntax is as follows &minus;</p><pre class="prettyprint notranslate">SELECT *FROM yourTableName WHERE LENGTH(yourColumnName) &lt; 5;</pre><p>To understand the above syntax, let us create a table. The query to create a table is as follows &minus;</p><pre class="prettyprint notranslate">mysql&gt; create table fieldLessThan5Chars &nbsp; &nbsp;-&gt; ( &nbsp; &nbsp;-&gt; Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, &nbsp; &nbsp;-&gt; yourZipCode varchar(10) &nbsp; &nbsp;-&gt; ); Query OK, 0 rows affected (0.52 sec)</pre><p>Now you can insert some records in the table using insert command. The query is as follows &minus;</p><pre class="prettyprint notranslate">mysql&gt; insert into fieldLessThan5Chars(yourZipCode) values(&#39;35801&#39;); Query OK, 1 row affected (0.10 sec) mysql&gt; insert into fieldLessThan5Chars(yourZipCode) values(&#39;3580&#39;); Query OK, 1 row affected (0.20 sec) mysql&gt; insert into fieldLessThan5Chars(yourZipCode) values(&#39;90001&#39;); Query OK, 1 row affected (0.40 sec) mysql&gt; insert into fieldLessThan5Chars(yourZipCode) values(&#39;100&#39;); Query OK, 1 row affected (0.20 sec) mysql&gt; insert into fieldLessThan5Chars(yourZipCode) values(&#39;10&#39;); Query OK, 1 row affected (0.17 sec) mysql&gt; insert into fieldLessThan5Chars(yourZipCode) values(&#39;0&#39;); Query OK, 1 row affected (0.15 sec) mysql&gt; insert into fieldLessThan5Chars(yourZipCode) values(&#39;90209&#39;); Query OK, 1 row affected (0.11 sec) mysql&gt; insert into fieldLessThan5Chars(yourZipCode) values(&#39;33124&#39;); Query OK, 1 row affected (0.20 sec)</pre><p>Display all records from the table using select statement. The query is as follows &minus;</p><pre class="prettyprint notranslate">mysql&gt; select *from fieldLessThan5Chars;</pre><p>The following is the output &minus;</p><pre class="result notranslate">+----+-------------+ | Id | yourZipCode | +----+-------------+ | &nbsp;1 | 35801 &nbsp; &nbsp; &nbsp; | | &nbsp;2 | 3580 &nbsp; &nbsp; &nbsp; &nbsp;| | &nbsp;3 | 90001 &nbsp; &nbsp; &nbsp; | | &nbsp;4 | 100 &nbsp; &nbsp; &nbsp; &nbsp; | | &nbsp;5 | 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | &nbsp;6 | 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | | &nbsp;7 | 90209 &nbsp; &nbsp; &nbsp; | | &nbsp;8 | 33124 &nbsp; &nbsp; &nbsp; | +----+-------------+ 8 rows in set (0.00 sec)</pre><h2>Example</h2><p>Here is the query to fetch all rows where a field value is less than 5 characters &minus;</p><pre class="prettyprint notranslate">mysql&gt; select *from fieldLessThan5Chars where length(yourZipCode) &lt; 5;</pre><h2>Output</h2><pre class="result notranslate">+----+-------------+ | Id | yourZipCode | +----+-------------+ | &nbsp;2 | &nbsp; &nbsp; &nbsp; &nbsp;3580 | | &nbsp;4 | 100 | | &nbsp;5 | 10 | | &nbsp;6 | 0 | +----+-------------+ 4 rows in set (0.00 sec)</pre>
raja
Updated on 26-Jun-2020 10:29:35

Advertisements