PostgreSQL String Functions and Operators with examples

String Functions in PostgreSQL are commonly and frequently used ones.

OperatorExample
Concatenate (||)select ‘SQL’ || ‘and’ || ‘PLSQL.com’; → SQLandPLSQL.com
select 42|| 42; → Error
select 42::varchar || 42; → 4242
bit_lengthselect bit_length(‘a’); → 8
select bit_length(‘抗’) ; → 24
char_lengthselect char_length(‘a’); → 1
select char_length(‘ab’); → 2
select char_length(‘抗’); → 1
lowerselect lower(‘ABC’); → abc
normalize
(Converts the string to the specified Unicode normalization form)
select normalize(U&’\0061\0308bc’);→ äbc
octet_length (Returns number of bytes in the string)select octet_length(‘äbc’); → 4
select octet_length(‘抗’); → 3
positionselect position(‘og’ in ‘google’); → 3
substringselect substring(‘Google’ from 2 for 3); → oog
select substring(‘Google’ from 3); → ogle
select substring(‘Google’ for 2); → Go
trimselect trim(both ‘xyz’ from ‘yxGooglexx’); → Tom
select trim(both ‘ ‘ from ‘ Goo ggle ‘); — Goo ggle
upperselect upper(‘abc’); → ABC
asciiselect ascii(‘A’); → 65
select ascii(‘a’); → 97
btrimselect btrim(‘xyxgoogleyyx’, ‘xyz’); → google
chrselect chr(65); → A
select chr(97); → a
concatselect concat(‘goo’, ‘g’, NULL, ‘le’); → google
concat_wsselect concat_ws(‘,’, ‘google’,’inc’); → google,inc
formatselect 1 << 4; v 16
initcapselect initcap(‘google inc’); → Google inc
leftselect left(‘google inc’,6); → google
lengthselect length(‘google’); → 6
lpadselect lpad(‘google’,12, ‘yahoo’); → yahoogoogle
ltrimselect ltrim(‘zyzgooglexyz’, ‘xyz’); → googlexyz
md5select md5(‘abc’); → 900150983cd24fb0d6963f7d28e17f72
parse_identselect parse_ident(‘”macbook”.depttable’); → {macbook,depttable}
quote_identselect quote_ident(‘google inc’); → “google inc”
quote_literalselect quote_literal(E’O\’Reilly’);→ ‘O”Reilly’
select quote_literal(42.5) → ‘42.5’
quote_nullableselect quote_nullable(‘NULL’); → NULL
select quote_nullable(15); → ’15’
regexp_matchselect regexp_match(‘google.com’, ‘google’); → {google}
select regexp_match(‘google.com’, ‘(google)(com’); → {google,inc}
regexp_replaceselect regexp_replace(‘googleinc’, ‘inc’,’.com’); → google.com
regexp_split_to_arrayselect regexp_split_to_array(‘google inc’, ‘\s+’); → {google,inc}
regexp_split_to_tableselect regexp_split_to_table(‘google inc’, ‘\s+’); → google,
in
repeatselect repeat(‘ha’, 4); → hahahaha
select repeat(‘1’, 3); → 111
replaceselect replace(‘google inc’, ‘ inc’, ‘.com’); → google.com
reverseselect reverse(‘malayalam’); → malayalam
select reverse(‘abc’); → cba
rightselect right(‘google.com’,3); → com
rpadselect rpad(‘google’,12, ‘yahoo’); → googleyahoo
rtrimselect rtrim(‘google ‘); → google
select rtrim(‘google.com’,’.com’); → google
split_partselect split_part(‘abc~@~def~@~ghi’, ‘~@~’, 2); → def
select split_part(‘abc,def,ghi,jkl’, ‘,’, -2); → ghi
strposselect strpos(‘google.com’,’.com’); → 7
substrselect substr(‘google.com’,7); → .com
select substr(‘google.com’,7,1); → .
starts_withselect starts_with(‘google.com’, ‘google’);→ true
string_to_arrayselect string_to_table(‘google.yahoo.bing’,’.’); → {google,yahoo,bing}
string_to_tableselect string_to_table(‘google.yahoo.bing’,’.’); →
google
yahoo
bing
to_asciiselect to_ascii(‘Karél’) → Karel
to_hexselect to_hex(2147483647) → 7fffffff
translateselect translate(‘google inc’, ‘ inc’, ‘.com’); → google.com
unistrselect unistr(‘d\0061t\+000061’); → data
select unistr(‘d\u0061t\U00000061’); → data
upperselect upper(‘google’); → GOOGLE

Format

Format specifiers are introduced by a % character and have the form

%[position][flags][width]type where

flags (optional)

width (optional)

type (required)

There are three type of ‘type’ allowed in format

%s – string

%I – SQL Identifier

%L – SQL Literal

Examples:-

ExampleResult
SELECT format(‘Testing %s, %s, %s’, ‘one’, ‘two’, ‘three’);Testing one, two, three
SELECT format(‘Hello %s %s’, ‘World’,’World’);Hello World World
SELECT format(‘INSERT INTO %I VALUES(%L)’, ‘locations’, ‘C:\ProgramFiles’);INSERT INTO locations VALUES(E’C:\ProgramFiles’)
SELECT format(‘|%10s|’,’google’)| google|
SELECT format(‘|%-10s|’,’google’)|google |
SELECT format(‘|%*s|’,10, ‘google’);| google|
SELECT format(‘|%*s|’,-10, ‘google’);|google |
SELECT format(‘Testing %3$s, %2$s, %1$s’, ‘one’, ‘two’, ‘three’);Testing three, two, one

%I and %L used to construct dynamic sql’s

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: