Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL SELECT statement for the "length" of the field is greater than 1

Tags:

mysql

I have an LINK field in my table. Some rows have a link, some don't.

I'd like to select all rows where LINK is present. (length is greater than X characters).

How do I write this?

like image 783
TIMEX Avatar asked Oct 09 '09 18:10

TIMEX


People also ask

How do I find the length of a column in MySQL?

select char_length(col. column_name) as column_name_length, count(*) as columns, count(distinct tab. table_name) as tables from information_schema. tables as tab inner join information_schema.

How do I select a field length in SQL?

SQL Server LEN() Function The LEN() function returns the length of a string. Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length.

How do I find the maximum length of a string in MySQL?

To work out the length of the largest string in a MySQL table, combine the LENGTH() and MAX() functions together like so: SELECT MAX(LENGTH(field_to_query)) FROM table_to_query; where "field_to_query" is the fieldname you want to query and table_to_query is the table.


1 Answers

How about:

SELECT * FROM sometable WHERE CHAR_LENGTH(LINK) > 1 

Here's the MySql string functions page (5.0).

Note that I chose CHAR_LENGTH instead of LENGTH, as if there are multibyte characters in the data you're probably really interested in how many characters there are, not how many bytes of storage they take. So for the above, a row where LINK is a single two-byte character wouldn't be returned - whereas it would when using LENGTH.

Note that if LINK is NULL, the result of CHAR_LENGTH(LINK) will be NULL as well, so the row won't match.

like image 128
Jon Skeet Avatar answered Sep 22 '22 20:09

Jon Skeet