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_idtitleliteral_textcolumn_value
1Sunny loftParisParis
2Quiet studioParisparis
3O'Hara HouseParisDublin
4Harbor viewParisBoston
5Garden flatParisParis

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'
listings
listing_idtitlecitypostal_codebedrooms
1Sunny loftParis750031
2Quiet studioparis750110
3O'Hara HouseDublinD023
4Harbor viewBoston021102
5Garden flatParis 750041
Result
listing_idtitle
3O'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'
listings
listing_idcity
1Paris
2paris
3Dublin
4Boston
5Paris

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
listings
listing_idtitlecitypostal_codebedrooms
1Sunny loftParis750031
2Quiet studioparis750110
3O'Hara HouseDublinD023
4Harbor viewBoston021102
5Garden flatParis 750041
Result
listing_idcityexact_match
1Paris1
2paris0
3Dublin0
4Boston0
5Paris 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.

expressionresultwhy
'10' < '9'true'1' comes before '9', comparison stops there
10 < 9falsenumeric comparison of whole values
CAST('10' AS INTEGER) < 9falsethe CAST makes it numeric
CAST('02110' AS INTEGER)2110the leading zero is lost
CAST(2110 AS VARCHAR)'2110'4 characters of text
'Paris' = 'paris'falseP and p are different characters

Each expression below compares 2 values. Which one returns true?

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.

filterwhat happens on listings
postal_code = '02110'keeps listing 4
postal_code = 02110type error: VARCHAR compared with INTEGER
CAST(postal_code AS INTEGER) > 75000error 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.