String Data Type
Concepts covered: Basic Types
Text lives in VARCHAR columns
A VARCHAR column holds a sequence of characters: letters, digits, spaces and punctuation, in whatever order someone entered them. The database stores each character and attaches no meaning to it. A postal code of '75003' is 5 characters that happen to be digits, and the database compares it with other text the way it compares any string, character by character, without turning it into a number first.
Single quotes mark a value, double quotes name a column
Text you type into a query goes inside single quotes: 'Paris'. Double quotes do a different job: they name a column or a table. "city" refers to the column called city, while 'city' is a 4-letter word. A query that wraps a value in double quotes asks for a column with that name, and when no such column exists the query fails before reading a single row.
SELECT
listing_id,
title,
'Paris' AS literal_text,
"city" AS column_value
FROM listings| listing_id | title | literal_text | column_value |
|---|---|---|---|
| 1 | Sunny loft | Paris | Paris |
| 2 | Quiet studio | Paris | paris |
| 3 | O'Hara House | Paris | Dublin |
| 4 | Harbor view | Paris | Boston |
| 5 | Garden flat | Paris | Paris |
An apostrophe inside the text
A title like O'Hara House contains an apostrophe, and an apostrophe is the character that ends a quoted value. Writing 'O'Hara House' closes the text after the O and leaves Hara House dangling as a syntax error. Doubling the apostrophe tells the database that the quote belongs to the text: 'O''Hara House' is read as 12 characters with 1 apostrophe in them. The doubling exists only in the query; the stored value and the result both show a single apostrophe.
SELECT
listing_id,
title
FROM listings
WHERE title = 'O''Hara House'| listing_id | title | city | postal_code | bedrooms |
|---|---|---|---|---|
| 1 | Sunny loft | Paris | 75003 | 1 |
| 2 | Quiet studio | paris | 75011 | 0 |
| 3 | O'Hara House | Dublin | D02 | 3 |
| 4 | Harbor view | Boston | 02110 | 2 |
| 5 | Garden flat | Paris | 75004 | 1 |
| listing_id | title |
|---|---|
| 3 | O'Hara House |
Comparison is exact, character by character
The = operator on 2 strings returns true only when every character matches: same letters, same case, same spaces. 'Paris' and 'paris' differ at their first character, so they are 2 different values. 'Paris ' with a trailing space has 6 characters and 'Paris' has 5, so those differ as well. Exactness is why text typed by people causes so much work: a human reads paris and Paris as one city, and the database counts 2. The listings table below has 3 spellings of Paris, and a filter on the exact word keeps only 1 of them.
SELECT
listing_id,
city
FROM listings
WHERE city = 'Paris'| listing_id | city |
|---|---|
| 1 | Paris |
| 2 | paris |
| 3 | Dublin |
| 4 | Boston |
| 5 | Paris |
Listing 1 is the only survivor. Listing 2 fails on case, and listing 5 fails on a space nobody can see on screen. When a filter drops a row you expected to keep, the fastest diagnosis is to remove the WHERE clause and show the comparison itself as a BOOLEAN column, so every row reports true or false next to the value it holds.
SELECT
listing_id,
city,
city = 'Paris' AS exact_match
FROM listings| listing_id | title | city | postal_code | bedrooms |
|---|---|---|---|---|
| 1 | Sunny loft | Paris | 75003 | 1 |
| 2 | Quiet studio | paris | 75011 | 0 |
| 3 | O'Hara House | Dublin | D02 | 3 |
| 4 | Harbor view | Boston | 02110 | 2 |
| 5 | Garden flat | Paris | 75004 | 1 |
| listing_id | city | exact_match |
|---|---|---|
| 1 | Paris | 1 |
| 2 | paris | 0 |
| 3 | Dublin | 0 |
| 4 | Boston | 0 |
| 5 | Paris | 0 |
Digits stored as text are still text
A VARCHAR made of digits compares like text, from the first character onward. '10' < '9' is true: the comparison looks at '1' against '9', finds that '1' comes first, and never reaches the second character. Numbers that nobody adds or averages belong in VARCHAR anyway. A postal code '02110' stored as INTEGER becomes 2110, and the leading zero is gone for good. When a text column needs arithmetic, CAST it to a number; when a number needs to sit inside text, CAST it to VARCHAR. The table below shows both directions and what each comparison returns.
| expression | result | why |
|---|---|---|
| '10' < '9' | true | '1' comes before '9', comparison stops there |
| 10 < 9 | false | numeric comparison of whole values |
| CAST('10' AS INTEGER) < 9 | false | the CAST makes it numeric |
| CAST('02110' AS INTEGER) | 2110 | the leading zero is lost |
| CAST(2110 AS VARCHAR) | '2110' | 4 characters of text |
| 'Paris' = 'paris' | false | P and p are different characters |
Each expression below compares 2 values. Which one returns true?
- 'Paris' = 'paris', because the database ignores case when comparing text
- '10' < '9', because text compares one character at a time from the left
- 'Paris ' = 'Paris', because a trailing space is dropped before comparing
- CAST('10' AS INTEGER) < 9, because the CAST keeps the text ordering
Quote the value, CAST the column
A value you compare against a VARCHAR column goes in single quotes: WHERE postal_code = '02110' keeps listing 4. Drop the quotes and the right side becomes the number 2110. A VARCHAR then meets an INTEGER, and the query stops with a type error. A text column you need as a number gets a CAST, and every row must then hold digits only: CAST(postal_code AS INTEGER) > 75000 fails on listing 3, because its code 'D02' has no integer to become.
| filter | what happens on listings |
|---|---|
| postal_code = '02110' | keeps listing 4 |
| postal_code = 02110 | type error: VARCHAR compared with INTEGER |
| CAST(postal_code AS INTEGER) > 75000 | error on listing 3: 'D02' cannot become an INTEGER |
| CAST(bedrooms AS VARCHAR) = '1' | keeps listings 1 and 5 |
About this lesson
This section is part of the String Functions: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.