Select Something That Has More/Less Than X Character

Select something that has more/less than x character

If you are using SQL Server, Use the LEN (Length) function:

SELECT EmployeeName FROM EmployeeTable WHERE LEN(EmployeeName) > 4

MSDN for it states:

Returns the number of characters of the specified string expression,

excluding trailing blanks.

Here's the link to the MSDN

For oracle/plsql you can use Length(), mysql also uses Length.

Here is the Oracle documentation:

http://www.techonthenet.com/oracle/functions/length.php

And here is the mySQL Documentation of Length(string):

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length

For PostgreSQL, you can use length(string) or char_length(string). Here is the PostgreSQL documentation:

http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-SQL

Query results that have less than X characters in MySQL

SELECT `post_id` FROM `wp_postmeta` WHERE `meta_key` = 'location' AND CHAR_LENGTH(meta_value) < 10

How to select data items of a certain length?

If you are bound to use a specific RDBMS then the solution is easy.

Use the LENGTH function.

Depending upon your database the length function can be LEN, Length, CarLength. Just search google for it.

According to your question

How do I select the row of a column such that the row size is <= 5 ?
Is there a query for this which will work on most/all databases ?

solution can be

SELECT * FROM TableName WHERE LENGTH(name) <= 5

If you want something that can work with almost all the database and I assume that the length of your string that you want to fetch is of a significant small length. Example 5 or 8 characters then you can use something like this

 SELECT * 
 FROM tab
 WHERE
    colName LIKE ''
 OR colName LIKE '_' 
 OR colName LIKE '__'
 OR colName LIKE '___'
 OR colName LIKE '____'
 OR colName LIKE '_____'

This works with almost all major DBMS.

see example:

SQL Server

MySQL

Oracle

Postgre SQL

SQLite

Select Columns Only if String length is greater than 2

GIANT EDIT

Although I agree with @Joro's approach, I realised there is a slightly more verbose but simpler way.

I created a copy of your table and called it Lessons, but I only put 12 Lessons in it, but you can generate your query in the same way.

Using the following query (which uses INFORMATION_SCHEMA.COLUMNS):

SELECT  'SELECT ID, ''' + COLUMN_NAME + ''' AS LessonName, 
        [' + COLUMN_NAME + '] AS Lesson ' +
+       'FROM Lesson WHERE ID = @ID AND LEN([' + COLUMN_NAME + ']) > 2 UNION' 

FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = 'Lesson'
AND DATA_TYPE = 'varchar'

I generate a query that looks like:

SELECT ID, 'Lesson 1' AS LessonName, [Lesson 1] AS Lesson  
FROM Lesson WHERE ID = @ID AND LEN([Lesson 1]) > 2 UNION
SELECT ID, 'Lesson 2' AS LessonName, [Lesson 2] AS Lesson  
FROM Lesson WHERE ID = @ID AND LEN([Lesson 2]) > 2 UNION
... (SQL omitted for brevity)
SELECT ID, 'Lesson 12' AS LessonName, [Lesson 12] AS Lesson  
FROM Lesson WHERE ID = @ID AND LEN([Lesson 12]) > 2 UNION

Removing the last UNION and running the query by declaring @ID as 35 gives me:

|| ID || LessonName || Lesson
|| 35 || Lesson 4   || Maths
|| 35 || Lesson 9   || ICT
|| 35 || Lesson 12  || English

I then thought to myself, well, I could probably just pivot this using the technique above... but then I had another though - the columns we actually want are in the LessonName column, so, we could probably just run a dynamic SQL query with those column names in:

DECLARE @ColumnList VARCHAR(MAX)

SELECT @ColumnList = COALESCE(@ColumnList + ', ','') + '[' + Lessons.LessonName + ']'

FROM ( 
SELECT ID, 'Lesson 1' AS LessonName, [Lesson 1] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 1]) > 2 UNION
SELECT ID, 'Lesson 2' AS LessonName, [Lesson 2] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 2]) > 2 UNION
...
SELECT ID, 'Lesson 12' AS LessonName, [Lesson 12] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 12]) > 2) 

AS Lessons

Which gives me the result '[Lesson 4], [Lesson 9], [Lesson 12]'

Which, in turn, you could do the following with:

DECLARE @QuerySQL NVARCHAR(MAX)

SET @QuerySql = 'SELECT ' + CAST(@ID AS VARCHAR) + ' AS ID, ' + @ColumnList + ' FROM Lesson WHERE ID = @ID'

--Query actually looks like: SELECT 35 AS ID, [Lesson 4], [Lesson 9], [Lesson 12] 
--                           FROM Lesson WHERE ID = 35  

DECLARE @ID INT --You will already have done this above anyway really
SET @ID = 35    

EXEC sp_executeSQL @QuerySql,N'@ID int', @ID

Which returns:

|| ID || Lesson 4 || Lesson 9 || Lesson 12 
|| 35 || Maths    || ICT      || English

An alternative approach to using pivot functions - you can easily generate this sql once and leave it in a stored procedure.

So, to put this altogether, your usage would look like:

DECLARE @ID INT
SET @ID = 35

DECLARE @ColumnList VARCHAR(MAX)

SELECT @ColumnList = COALESCE(@ColumnList + ', ','') + '[' + Lessons.LessonName + ']' 

FROM (  
SELECT ID, 'Lesson 1' AS LessonName, [Lesson 1] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 1]) > 2 UNION 
SELECT ID, 'Lesson 2' AS LessonName, [Lesson 2] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 2]) > 2 UNION 
... 
SELECT ID, 'Lesson 35' AS LessonName, [Lesson 35] AS Lesson  FROM Lesson WHERE ID = @ID AND LEN([Lesson 35]) > 2)  

AS Lessons --Remember you can generate this section quite simply using information_schema.columns
           --and you don't actually need the ID or Lesson columns - just the lesson names.

DECLARE @QuerySQL NVARCHAR(MAX) 

SET @QuerySql = 'SELECT ' + CAST(@ID AS VARCHAR) + ' AS ID, ' + @ColumnList + ' FROM Lesson WHERE ID = @ID'     

EXEC sp_executeSQL @QuerySql,N'@ID int', @ID 

Which will give you the answer you want.

Note that pivoting the data at the GUI level (either via an Excel Pivot table or a 3rd party component like the DevExpress Pivot Grid means you can stop at the first resultset - the output of the UNION queries)

select rows has numeric length more than x

Assuming that you do not know how long the character portions are, you can use regular expressions:

select u.*
from users u
where u.username regexp '^.*[0-9]{7}.*$';

Actually, this can be simplified to:

select u.*
from users u
where u.username regexp '[0-9]{7}';

When using regular expressions, I just like to be careful. LIKE patterns match the whole string (from the beginning to the end). REGEXP patterns match within a string.

Select rows where a value on table x is 1 greater than the same value on table y

I think the solution could be like this:

select * 
from X join Y on X.Total = Y.Sum + 1 and X.Name = Y.Name;

Find all database records where the char data length in a column is larger than X

I suggest you create a function that calculate length of notes,receiving as parameter your table id (I asumme tbl_name_id) like this:

CREATE OR REPLACE function get_length(val long) return number
is
res long;
begin
    select notes into res from tbl_name where val = tbl_name_id;
    return length(res);  
end;

And then you can do this:

select * from tbl_name where get_length(tbl_name_id) > 1

You can also see here http://www.techonthenet.com/oracle/questions/long_length.php

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

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.

Selecting productid from a table that has at least 3 feedbacks greater than 3

select t.prodid,count(t.prodid) from table as t 
where not exists 
(select 1 from table as t2 where t2.prodid=t.prodid and t2.feedback<4) 
group by t.prodid 
having count(*)>=3 

---

Related Topics