I. Common formatting functions
1. Date Turn string
Select To_char (Current_timestamp, ' yyyy-mm-dd HH24:MI:SS ')//2017-09-18 22:41:50
YYYY: Year (4 and more bits)
MM: Month number (01-12)
DD: Day of one months (01-31)
HH24: Hours of the day (00-23)
MI: minutes (00-59)
SS: SEC (00-59)
2. String Turn date
Select To_date (' 2017-09-18 ', ' yyyy-mm-dd ')//2017-09-18,to_date function return date
Select To_timestamp (' 2017-09-18 22:41:50 ', ' yyyy-mm-dd HH24:MI:SS ')//2017-09-18 22:41:50,to_timestamp function returns datetime
3. Numeric to String
Select 123.45 | | '//Get ' 123.45 ' string
4. String to Number
Select ' 123.45 ':: Numeric num//Get 123.45,java type BigDecimal type
Select ' 123.45 ':: Double precision num//Get 123.45,java type Double type
:: Numeric and:: Double precision can convert null, but cannot convert empty string (SQL will error)
Select null:: Numeric num//Get NULL
Select null:: Double precision num//Get NULL
Two. Common String functions
1. String concatenation
Select ' Ab ' | | ' C '//Get ' abc ' string
2. Number of bits in a string (1 bytes equals 8 bits)
Select Bit_length (' abc ')//Get 24
1 English characters are 1 bytes, 3 English characters account for 3 bytes, 24 bits
Select Bit_length (' Chinese ')//Get 72
1 Chinese characters account for 3 bytes, 3 Chinese characters are 9 bytes, 72 bits
3. Length of the string
Select Length (' abc ')//Get 3
Select Length (' Chinese ')//Get 3
4. String substitution
Replace by index location:
Select Overlay (' Txxas ' placing ' Om ' from 2 for 2)//Get ' Tomas ' string
Overlay itself is the meaning of "covering."
The integer following the from is the index, where the substitution begins. The index here starts at 1, that is, the first character index is 1. If the From is omitted, the substitution begins with the first character.
The integer following the for represents how many characters are replaced. For cannot omit
Replace by string match:
Select replace (' Txxas ', ' xx ', ' Om ')//Get ' Tomas ' string
If more than one substring can be matched, all are replaced
5. Where to take the substring
Select position (' Om ' in ' Thomas ')//Get 3
If 0 is returned, there is no such substring.
6. Take substring
Select substring (' Tomas ' from 2 for 2)//Get ' Om ' string
The meaning of from and for is the same as the overlay () function
7. Remove a character from the beginning/end/sides of a string, and return the remaining string
Select Trim ([leading | trailing | both] ' x ' from ' xabcx ')//if it is leading, get ' abcx ' string, if trailing, get ' xabc ' character changer If it's both or not, get the ' abc ' string
Select Trim (' abc ')//delete null characters on both sides to get the ' abc ' string
8. String segmentation
Select Split_part (' Ab,bc,cd,de ', ', ', 1)//Get ' AB ' string
The ' Ab,bc,cd,de ' string is separated by a comma, returning the 1th string.
The above functions are compatible with PostgreSQL.
SQL common formatting functions and String functions