Preface
PostgreSQL database is referred to as pg database for short.
This article mainly introduces some common operations of strings when using pg database.
For example: how multiple strings are concatenated together, how strings are converted in case, delete spaces on both sides of strings, find character positions, find substrings, etc.
1. How to connect and splice multiple strings?
Use ||, note that it is not +
1. Splice 2 strings hello and word together
SELECT 'hello' || 'world';--result: helloworldSELECT 'hello' || 'world'; --result: helloworld
2. Splice 3 strings hello, spaces and word together
SELECT 'hello' || ' ' || 'world'; --result：hello world
3. Splice the string hello and the number 123456 together
SELECT 'hello' || 123456; --result：hello123456
2. String case conversion
1. Convert Hello World to lowercase
SELECT lower('Hello World');
--result：hello world
2. Convert Hello World to capital
SELECT upper('Hello World');
--result：HELLO WORLD
3. Delete the spaces on both sides of the string
SELECT trim(' hello world ');
--result：hello world
4. Find the character position
Note: The return value of the position function starts from 1, not the subscript value starting from 0. If 0 is returned, no character is found.
1. Find the position of @ in the string hello@
SELECT position('@' IN 'hello@');
--result：6
2. Find the position of b in the string hello@
Note: Because b is not in the string hello@, returning 0 means that the character b is not found.
SELECT position('b' IN 'hello@');
--result：0
5. Find substrings
Function: substring('hello@', start, count);
Parameter 1: string, parameter 2: start position, parameter 3: count
Note: the difference between the start position and count value
Query substring hello
Method 1. start=1,count=5
SELECT substring('hello@',1,5);
--result：hello
Method 2. start=0, count=6
SELECT substring('hello@',0,6);
--result：hello
6. Comprehensive examples
Function description: Convert Hello@ to lowercase and change the domain name from
Hello@ --> hello@
SELECT lower(substring('Hello@',0, position('@' IN 'Hello@')) || '@');
--result：hello@
SELECT lower(substring('Hello@',1, position('@' IN 'Hello@') - 1) || '@');
--result：hello@
Summarize
The above is what we will talk about today. This article only briefly introduces the use of some commonly used functions of strings in the pg database. PG also provides a large number of functions and methods. For details, please refer to the pg official website./docs/current/。
This is the article about PostgreSQL database string splicing, case conversion and substring detailed explanation. For more related PostgreSQL string splicing, upper and lower case conversion content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!