CODEWITHSUNDEEP
mysql
TIMEX
TIMEX

Reputation: 272372

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

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?

Upvotes: 89

Views: 152722

Answers (4)

user47589
user47589

Reputation: 

select * from [tbl] where [link] is not null and len([link]) > 1

For MySQL user: 

LENGTH([link]) > 1

Upvotes: 16

alizelzele
alizelzele

Reputation: 923

Just in case anybody want to find how in oracle and came here (like me), the syntax is

select length(FIELD) from TABLE

just in case ;)

Upvotes: 7

KM.
KM.

Reputation: 103707

Try:

SELECT
    *
FROM
    YourTable
WHERE
    CHAR_LENGTH(Link) > x

Upvotes: 7

Jon Skeet
Jon Skeet

Reputation: 1503859

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.

Upvotes: 169

Related Questions