Mysql/SQL Retrieve First 40 Characters of a Text Field

MySQL/SQL retrieve first 40 characters of a text field?

SELECT LEFT(field, 40) AS excerpt FROM table(s) WHERE ...

See the LEFT() function.

As a rule of thumb, you should never do in PHP what MySQL can do for you. Think of it this way: You don't want to transmit anything more than strictly necessary from the DB to the requesting applications.

---

EDIT If you're going to use the entire data on the same page (i.e., with no intermediate request) more often than not, there's no reason not to fetch the full text at once. (See comments and Veger's answer.)

MySQL Select Query - Get only first 10 characters of a value

Using the below line

SELECT LEFT(subject , 10) FROM tbl 

MySQL Doc.

how to select characters after first 20 characters from field mysql

To get characters after the first 20 characters (note that if there are not twenty characters, the function will return an empty string):

SELECT SUBSTRING('Some Random Address That is Longer than 20 characters' FROM 20);

Now if you need address 2 to be NULL, you check character length first:

SELECT if(char_length(address) > 20, SUBSTRING(address FROM 20), NULL);

To get the first 20 characters, you can use the substring function like this:

SELECT SUBSTRING('Some Random Address', 1, 20);

Now the final query could look like this:

SELECT SUBSTRING(address, 1, 20) as Address1, 
    IF(CHAR_LENGTH(address) > 20, SUBSTRING(address FROM 20), NULL) as Address2
FROM customer

Get first characters from a String

Edit:

In My SQL, you could use LEFT (or SUBSTRING) to SELECT certain number of characters from a string. Since what you need is the first characters, I suggest to use LEFT:

SELECT LEFT(field_value, 20) FROM MyTableName

Original:

(As the tag was originally C#, the following solution below provides solution for C# problem)

You should use Substring(0, 20). The first argument (0) is the starting index, and the second argument (20) is the length of the string you want to take.

For example:

var str = "123456789012345678901234567890";
str = str.Substring(0, 20);

SQL query to display the length and first 3 characters of ename column in emp table

Hi Shanu,
You can use LEN() or LENGTH()(in case of oracle sql) function to get the length of a column.

SELECT LEN(column_name) FROM table_name;

And you can use SUBSTRING or SUBSTR() function go get first three characters of a column.

SUBSTRING( string, start_position, length );
SELECT SUBSTRING( column_name, 1, 3 ) FROM table_name;

To get both together use concatenation operator,

 SELECT LEN(column_name)||SUBSTRING( column_name, 1, 3 ) FROM table_name;

Hope you got what you need. Any issues, feel free to ask

How can I select the first 100 characters in SQL Server?

Try this:

 SELECT LEFT (your_column, 100) FROM your_table 

Edit:

you can also try something like this:

  SELECT LEFT (your_column, LEN(your_column)-5) FROM your_table 

for say if you want to trim the last 5 characters from a record.

How to select titles that are like the first n characters of a string?

for filter a string for a partial fixed value you can use like operator like 'yourstring%' eg:

    SELECT title 
    FROM your_table  
    WHERE title LIKE 'thefirst10%'  

or use a string fuction like substr or left

    SELECT title 
    FROM your_table  
    WHERE left(title,10) = 'thefirst10'  

    SELECT title 
    FROM your_table  
    WHERE substr(title,1,10) = 'thefirst10'  

Limit length of longtext field in SELECT results

Use MySQL's SUBSTRING function, as described in the documentation. Like:

SELECT SUBSTRING(`text`, 1, 100) FROM blog_entry;

To select first 100 chars.

---

Related Topics