converting TEXT to VARCHAR
I 've noticed that when converting TEXT to VARCHAR the converted value is silently clipped at 30 characters.
CREATE TABLE foo (x TEXT)
-- insert a string that's 50 characters long
INSERT INTO foo(x) VALUES('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
SELECT CHAR_LENGTH(CONVERT(VARCHAR, x)) FROM foo -- retuns 30
SELECT CHAR_LENGTH(CONVERT(VARCHAR(3000), x)) FROM foo -- returns 50
My questions are:
- where is that limit documented / originate from?
- what's an idiomatic way to make the conversion without having to add an arbitrarily high value? (as in the second
SELECTstatement above)