Working with String Data

3 functions to deal with character data types:

• char
• varchar
• text

create table string_tbl
(char_fld char(30),
vchar_fld varchar(30),
text_fld text
);

String Generation

insert into string_tbl (char_fld, vchar_fld, text_fld)
values ( 'this is char data',
'this is varchar data',
'this is text data'
);

update string_tbl
set vchar_fld = 'This is a piece of extremely long varchar data'
where id = 1;

/* Error Code: 1406. Data too long for column 'vchar_fld' at row 1 */

Maximum length for vchar_fld is 30, while the string is 46 characters.

There are 2 behaviors:

• Strict: Exceptions are thrown when problems arise.
• Non-strict: The string will be truncated and a warning is issued.

<aside> 💡 It is recommended to set the upper limit of a column to a high value that can handle the longest strings.

</aside>

Single Quotes

update string_tbl
set text_fld = 'This string didn''t work, but it does now'
where id = 1;

<aside> 💡 If retrieving the string (for example: export), use built-in function quote(), it will add escapes to any quotes or apostrophes exist within the string: select quote(text_fld) from string_tbl;

</aside>

Special characters

SELECT CONCAT('danke sch', CHAR(148), 'n');
SELECT ASCII('ö');