SQL common formatting functions and String functions

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