I&#39;ve created a view which uses `GROUP_CONCAT` to concatenate results from a query on products column with data type of `&#39;varchar(7) utf8_general_ci&#39;` in a column named `concat_products`. 

The problem is that MySQL truncates value of &quot;concat_products&quot; column.
phpMyAdmin says the data type of &quot;concat_products&quot; column is `varchar(341) utf8_bin`

Table products:

	CREATE TABLE `products`(
	    `productId` tinyint(2) unsigned NOT NULL AUTO_INCREMENT, 
	    `product` varchar(7) COLLATE utf8_general_ci NOT NULL, 
	    `price` mediumint(5) unsigned NOT NULL, 
	    PRIMARY KEY (`productId`)
    ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

The &quot;concat_products_vw&quot; view:

	CREATE VIEW concat_products_vw AS
	SELECT
	  `userId`,
	  	GROUP_CONCAT(CONCAT_WS(&#39;_&#39;, `product`, `productId`, `price`) 
	  		ORDER BY `productId` ASC SEPARATOR &#39;*&#39;) AS concat_products
	FROM
	  `users`
	LEFT JOIN `products` 
	ON `users`.`accountBalance` &gt;= `product`.`price`
	GROUP BY `productId` 

According to MySQL manual:

&gt; Values in VARCHAR columns are variable-length strings&lt;br&gt;
Length can be specified as a value from 1 to 255 before MySQL 4.0.2 and 0 to 255 as of MySQL 4.0.2.

---
### EDIT

`Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.` 
	
1. Why MySQL specifies more than 255 characters for `varchar` &quot;concat_products&quot; column? (solved!)

2. Why `uf8_bin` instead of `utf8_general_ci`?

3. Is it possible to change the data type of a column in a *view* for example in my case to text for &quot;concat_products&quot; column?

4. If not what can I do to prevent MySQL from truncating &quot;concat_products&quot; column?

MySQL truncates concatenated result of a GROUP_CONCAT function

This i what I am trying to achieve:

If a double has more than 3 decimal places, I want to truncate any decimal places beyond the third. (do not round.)

    Eg.: 12.878999 -&gt; 12.878

If a double has less than 3 decimals, leave unchanged

    Eg.:   125   -&gt; 125
           89.24 -&gt; 89.24

I came across this command:

    double example = 12.34567;
    double output = Math.Round(example, 3);

But I do not want to round. According to the command posted above, 
`12.34567 -&gt; 12.346`

I want to truncate the value so that it becomes: `12.345`



Limiting double to 3 decimal places

I have this testingdatabase which, by now, is stuffed with junk. Now I&#39;ve done a few Table.destroy_all commands in the rails console which deletes all records and dependencies which is awesome. However; I&#39;d like to truncate everything so the ID&#39;s etc. start at 1 again. Is there any way in Rails 3?

Truncate table(s) with rails console

I would like to take the original URL, truncate the query string parameters, and return a cleaned up version of the URL. I would like it to occur across the whole application, so performing through the global.asax would be ideal. Also, I think a 301 redirect would be in order as well.

ie.

in: www.website.com/default.aspx?utm_source=twitter&amp;utm_medium=social-media

out: www.website.com/default.aspx


What would be the best way to achieve this?



Truncating Query String &amp; Returning Clean URL C# ASP.net

What&#39;s the best way to truncate a datetime value (as to remove hours minutes and seconds) in SQL Server 2008?

For example:

    declare @SomeDate datetime = &#39;2009-05-28 16:30:22&#39;
    select trunc_date(@SomeDate)
    
    -----------------------
    2009-05-28 00:00:00.000

How can I truncate a datetime in SQL Server?

For example, I might want to:

    tail -f logfile | grep org.springframework | &lt;command to remove first N characters&gt;

I was thinking that `tr` might have the ability to do this but I&#39;m not sure.

What is a unix command for deleting the first N characters of a line?


Earlier i was doing it dynamically using JS.. but we were getting some performance issues cuz of which we have to come with an alternative option. 

I am now truncating a long text on my tab names using text-overflow style.

but i have a small issue if some one can resolve it

currently this is how my text truncation looks like

**This text has been trun...**

Here the three dots (...) comes in black colour and i want to change it to red.

**Is there a way that we can achieve this one?**

Text-overflow CSS truncation

I need to empty an LDF file before sending to a colleague.  How do I force SQL Server to truncate the log?

What is the command to truncate a SQL Server log file?

Is there any good way of truncating text with plain HTML and CSS, so that dynamic content can fit in a fixed-width-and-height layout?

I&#39;ve been truncating server-side by _logical_ width (i.e. a blindly-guessed number of characters), but since a &#39;w&#39; is wider than an &#39;i&#39; this tends to be suboptimal, and also requires me to re-guess (and keep tweaking) the number of characters for every fixed width.  Ideally the truncation would happen in the browser, which knows the _physical_ width of the rendered text.

I&#39;ve found that IE has a `text-overflow: ellipsis` property that does exactly what I want, but I need this to be cross-browser.  This property [seems to be (somewhat?) standard](http://www.quirksmode.org/css/textoverflow.html) but isn&#39;t supported by Firefox.  I&#39;ve found [various](http://www.jide.fr/emulate-text-overflowellipsis-in-firefox-with-css)  [workarounds](https://stackoverflow.com/questions/480722/how-can-i-set-a-td-width-to-visually-truncate-its-displayed-contents) based on `overflow: hidden`, but they either don&#39;t display an ellipsis (I want the user to know the content was truncated), or display it all the time (even if the content wasn&#39;t truncated).

Does anyone have a good way of fitting dynamic text in a fixed layout, or is server-side truncation by logical width as good as I&#39;m going to get for now?

Truncating long strings with CSS: feasible yet?

I&#39;d like to truncate a dynamically loaded string using straight JavaScript.  It&#39;s a url, so there are no spaces, and I obviously don&#39;t care about word boundaries, just characters.  

Here&#39;s what I got:

    var pathname = document.referrer; //wont work if accessing file:// paths
    document.getElementById(&quot;foo&quot;).innerHTML = &quot;&lt;a href=&#39;&quot; + pathname +&quot;&#39;&gt;&quot; + pathname +&quot;&lt;/a&gt;&quot;

Truncate a string straight JavaScript

I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL?

At the moment I&#39;ve managed to come up with a SQL statement that returns all the commands I need to execute: 

    SELECT &#39;TRUNCATE TABLE &#39; ||  tablename || &#39;;&#39; FROM pg_tables WHERE tableowner=&#39;MYUSER&#39;;

But I can&#39;t see a way to execute them programmatically once I have them.

Truncating all tables in a Postgres database

Why doesn&#39;t a **TRUNCATE** on `mygroup` work?
Even though I have `ON DELETE CASCADE SET` I get:

&gt;ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`mytest`.`instance`, CONSTRAINT `instance_ibfk_1` FOREIGN KEY (`GroupID`) REFERENCES `mytest`.`mygroup` (`ID`))


    drop database mytest;
    create database mytest;
    use mytest;
    
    CREATE TABLE mygroup (
       ID    INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ) ENGINE=InnoDB;
    
    CREATE TABLE instance (
       ID           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       GroupID      INT NOT NULL,
       DateTime     DATETIME DEFAULT NULL,

       FOREIGN KEY 	(GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,
       UNIQUE(GroupID)
    ) ENGINE=InnoDB;

How to truncate a foreign key constrained table?

Is there a function that can truncate or round a Double?  At one point in my code I would like a number like: `1.23456789` to be rounded to `1.23`

Scala Doubles, and Precision

I have a `UILabel` that can be varying lengths depending on whether or not my app is running in portrait or landscape mode on an iPhone or iPad. When the text is too long to show on one line and it truncates I want the user to be able to press it and get a popup of the full text. 

How can I check to see if the `UILabel` is truncating the text? Is it even possible? Right now I&#39;m just checking for different lengths based on what mode I&#39;m in but it does not work super well.

How to check if UILabel is truncated?

For example I have the variable 3.545555555, which I would want to truncate to just 3.54. 

How can I truncate a double to only two decimal places in Java?

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?

I know that I can either

* Use a `DELETE` without a where clause and then `RESEED` the identity (or)
* Remove the FK, truncate the table, and recreate the FK.

I thought that as long as I truncated the child table before the parent, I&#39;d be okay without doing either of the options above, but I&#39;m getting this error:

&gt; Cannot truncate table &#39;TableName&#39; because it is being referenced by a FOREIGN KEY constraint.

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

I would like to truncate a string such that its length is not longer than a given value.  I am writing to a database table and want to ensure that the values I write meet the constraint of the column&#39;s datatype.

For instance, it would be nice if I could write the following:

    string NormalizeLength(string value, int maxLength)
    {
        return value.Substring(0, maxLength);
    }

Unfortunately, this raises an exception because `maxLength` generally exceeds the boundaries of the string `value`.  Of course, I could write a function like the following, but I was hoping that something like this already exists.

    string NormalizeLength(string value, int maxLength)
    {
        return value.Length &lt;= maxLength ? value : value.Substring(0, maxLength);
    } 

Where is the elusive API that performs this task?  Is there one?

How do I truncate a .NET string?

I am trying to write a Unix script which will truncate/empty a file which is continuously being written/open by an application when it reaches say 3GB of space. I know that the below command would do it : 

    cp /dev/null [filename]

But I am going to run this in an production environment automatically as a cron job - just posting here to see if you guys faced any issues while doing something similar to this. 


Unix shell script to truncate a large file

I&#39;m looking for a simple script which can truncate a string with ellipsis (...)

I want to truncate something like `&#39;this is a very long string&#39;` to `&#39;this is a ve...&#39;`


I don&#39;t want to use CSS or PHP.



I want to truncate a text or line with ellipsis using JavaScript

I have a file, `foo.txt`, containing the following lines:

    a
    b
    c

I want a simple command that results in the contents of `foo.txt` being:

    a
    b


Remove the last line from a file in Bash

According to [this forum discussion][1], SQL Server (I&#39;m using 2005 but I gather this also applies to 2000 and 2008) silently truncates any `varchar`s you specify as stored procedure parameters to the length of the varchar, even if inserting that string directly using an `INSERT` would actually cause an error.  eg. If I create this table:

    CREATE TABLE testTable(
    	[testStringField] [nvarchar](5) NOT NULL
    )

then when I execute the following:

    INSERT INTO testTable(testStringField) VALUES(N&#39;string which is too long&#39;)

I get an error:

    String or binary data would be truncated.
    The statement has been terminated.

Great.  Data integrity preserved, and the caller knows about it.  Now let&#39;s define a stored procedure to insert that:

    CREATE PROCEDURE spTestTableInsert
    	@testStringField [nvarchar](5)
    AS
    	INSERT INTO testTable(testStringField) VALUES(@testStringField)
    GO

and execute it:

    EXEC spTestTableInsert @testStringField = N&#39;string which is too long&#39;

No errors, 1 row affected.  A row is inserted into the table, with `testStringField` as &#39;strin&#39;.  SQL Server silently truncated the stored procedure&#39;s `varchar` parameter.

Now, this behaviour might be convenient at times but I gather there is NO WAY to turn it off.  This is extremely annoying, as I *want* the thing to error if I pass too long a string to the stored procedure.  There seem to be 2 ways to deal with this.

First, declare the stored proc&#39;s `@testStringField` parameter as size 6, and check whether its length is over 5.  This seems like a bit of a hack and involves irritating amounts of boilerplate code.

Second, just declare ALL stored procedure varchar parameters to be `varchar(max)`, and then let the `INSERT` statement within the stored procedure fail.

The latter seems to work fine, so my question is: is it a good idea to use `varchar(max)` ALWAYS for strings in SQL Server stored procedures, if I actually want the stored proc to fail when too long a string is passed?  Could it even be best practice?  The silent truncation that can&#39;t be disabled seems stupid to me.


  [1]: http://www.pcreview.co.uk/forums/thread-1241669.php


SQL Server silently truncates varchar&#39;s in stored procedures

I want to truncate a string as follows:

**input:**

    string = &quot;abcd asfsa sadfsaf safsdaf aaaaaaaaaa aaaaaaaaaa dddddddddddddd&quot;

**output:**

    string = &quot;abcd asfsa sadfsaf safsdaf aa...ddddd&quot;

Truncate string with Rails?

What&#39;s the difference between `TRUNCATE` and `DELETE` in SQL?

If your answer is platform specific, please indicate that.

What&#39;s the difference between TRUNCATE and DELETE in SQL

We&#39;re using `Postgresql 9.1.4` as our db server.  I&#39;ve been trying to speed up my test suite so I&#39;ve stared profiling the db a bit to see exactly what&#39;s going on.  We are using [database_cleaner](https://github.com/bmabey/database_cleaner) to truncate tables at the end of tests.  YES I know transactions are faster, I can&#39;t use them in certain circumstances so I&#39;m not concerned with that.

What I AM concerned with, is why TRUNCATION takes so long (longer than using DELETE) and why it takes EVEN LONGER on my CI server.

Right now, locally (on a Macbook Air) a full test suite takes 28 minutes.  Tailing the logs, each time we truncate tables... ie:

    TRUNCATE TABLE table1, table2  -- ... etc

it takes over 1 second to perform the truncation.  Tailing the logs on our CI server (Ubuntu 10.04 LTS), take takes a full 8 seconds to truncate the tables and a build takes 84 minutes.

When I switched over to the `:deletion` strategy, my local build took 20 minutes and the CI server went down to 44 minutes.  This is a *significant* difference and I&#39;m really blown away as to why this might be.  I&#39;ve [tuned](http://thebuild.com/presentations/not-your-job.pdf) [the](http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) DB on the CI server, it has 16gb system ram, 4gb shared_buffers... and an SSD.  All the good stuff.  How is it possible:

**a.** that it&#39;s SO much slower than my Macbook Air with 2gb of ram  
**b.** that TRUNCATION is so much slower than DELETE when the [postgresql docs][1] [state explicitly][2] that it should be much faster.

Any thoughts?


  [1]: http://www.postgresql.org/docs/9.1/static/sql-truncate.html
  [2]: http://www.postgresql.org/docs/9.1/static/sql-truncate.html#AEN75996

Postgresql Truncation speed

Can the SQL &quot;truncate table&quot; command be used within a transaction?  I am creating an app and my table has a ton of records.  I want to delete all the records, but if the app fails I was to rollback my transaction.  Deleting each record takes a very long time.  I&#39;m wondering if I use truncate table, can I still rollback the transaction and get my data back in the event of a failure.  I realize that truncate table doesn&#39;t write each delete to the transaction log, but I&#39;m wondering if it writes the page deallocation to the log so that rollback works.  

Truncate Table Within Transaction

I am trying to truncate some long text in C#, but I don&#39;t want my string to be cut off part way through a word. Does anyone have a function that I can use to truncate my string at the end of a word?

E.g:

    &quot;This was a long string...&quot;

Not:

    &quot;This was a long st...&quot;

Truncate string on whole words in .NET C#

Could someone give me a quick overview of the pros and cons of using the following two statements:

    TRUNCATE TABLE dbo.MyTable

vs

    DELETE FROM dbo.MyTable

It seems like they both do the same thing when all is said and done; but are there must be differences between the two.



Pros &amp; Cons of TRUNCATE vs DELETE FROM

I tried to truncate a table with foreign keys and got the message:

&gt; &quot;**Cannot truncate table  because it is being referenced by a FOREIGN KEY constraint**&quot;. 

I read a lot of literature about the problem and thought that I found the solution by using delete 

    DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)

But I still got an error message:

&gt; &quot;**The DELETE statement conflicted with the REFERENCE constraint**&quot;. 

When I try to delete with Microsoft Management Studio and execute the previous query 
    
    DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)

it doesn&#39;t give an error and works properly. I want to delete all information from a table and add new into it, but I don&#39;t want to drop and create foreign keys.



I got error &quot;The DELETE statement conflicted with the REFERENCE constraint&quot;

To empty a database table, I use this SQL Query:

    TRUNCATE TABLE `books`

How to I truncate a table using Django&#39;s models and ORM?

I&#39;ve tried this, but it doesn&#39;t work:

    Book.objects.truncate()

How to TRUNCATE TABLE using Django&#39;s ORM?

I&#39;m using C to write some data to a file. I want to erase the previous text written in the file in case it was longer than what I&#39;m writing now.
I want to decrease the size of file or truncate until the end. How can I do this?

All Truncate Solutions on Gang of Coders

Total of 30 Truncate Solutions