Ads by ProfitSence
Close

SQL String Manipulation

Last Updated on Wednesday 5th Oct 2022

LOWER()

The .lower() method for strings in Python is used to convert every letter in a string to a lower case.

Convert all letters of the string HeLlO, wOrLd! to lower case

			
					SELECT LOWER('HeLlO, wOrLd!')

			
	
  • In Case You Need to run a query that returns something but makes sure the letters are all lower case.
			
					SELECT LOWER(e.name)
FROM employees AS e
    INNER JOIN regions AS r ON e.code = r.code
WHERE r.cname = 'Aus'

			
	

UPPER()

Same as the .upper() method for strings in Python used to capitalize every letter in a row.

Capitalize all letters of the string Hello World.

			
					SELECT UPPER('Hello, world!')

			
	
  • In Case You Need to run a query that capitalizes every letter of something from some table.
			
					SELECT UPPER(d.abcd)
FROM abcds AS d

			
	

INITCAP()

Same as the .capitalize() method for strings in Python that is used to convert the first letter to upper case.

			
					SELECT INITCAP(e.name), INITCAP(e.detable)
FROM employees AS e
WHERE e.name ILIKE 'an%'

			
	

LENGTH()

Same as the len() function in Python. However, since we don't have lists or tuples in SQL, it is only applicable to objects with characters.

			
					SELECT e.name, e.abtab
FROM peoples AS e
WHERE e.abtab = 'Sports' AND LENGTH(e.name) >= 6

			
	

TRIM()

Same as the .strip() method for strings in Python that eliminates leading and trailing white spaces.

			
					SELECT TRIM('     Hello, world!     ')

			
	

SUBSTRING()

Syntax

			
					SELECT SUBSTRING(string_column FROM <start_position> FOR <num_characters_ahead>)

			
	
			
					SELECT SUBSTRING(string_column, <start_position>, <num_characters_ahead>)

			
	
			
					SELECT SUBSTRING('Hello there, friend! Hehe.' FROM 1 FOR 5)

			
	
			
					SELECT SUBSTRING('Hello there, friend! Hehe.', 1, 5)

			
	

Will returns 'Hello.'

Concatenation

Same as the string concatenation in Python using +. The + in Python is replaced by || in PostgreSQL. Alternatively, you can use the CONCAT() function.

			
					SELECT INITCAP(e.fname) || ' ' || INITCAP(e.lname)
FROM employees e

			
	

REPLACE()

It is equivalent to the .replace() method for strings in Python and the gsub() function in R.

			
					SELECT name,
       REPLACE(depart, 'Sci', 'Comm') AS dept
FROM employees

			
	

COALESCE()

The very Extremely powerful function lets us handle missing values on a column-by-column basis.

The syntax is pretty straight forward for this one:

			
					COALESCE(<column_name>, <fill_value>)

			
	
			
					SELECT first_name,
       COALESCE(email, '¯\_(ツ)_/¯') AS cleaned_email
FROM employees

			
	
sql string manipulation
string manipulation sql
sql string manipulation in select
string manipulation in sql
sql text manipulation
sql manipulate string