[teacher Zhao Qiang] the character function of SQL

Character functions, as the name implies, operate on strings. From the following figure, let's take a look at the character function of Oracle.

The case control functions lower, upper, initcap1 2select lower ('Hello World') are converted to lowercase, upper (' Hello World') to uppercase, and initcap ('hello world') to uppercase from dual

Character control function

Substr (aforme b) starts with bit b from a.

1select substr ('Hello World', 3) from dual

Substr (a dint b ~ C) starts with bit b in a, and takes bit c.

1select substr ('Hello World', 3pr 4) from dual

Number of length characters lengthb bytes

1 2 3 45 from dual;-for English, the number of characters is the same as the number of bytes: select length ('Hello World') characters, lengthb (' Hello World') bytes from dual;-for Chinese, the number of characters equals two bytes select length ('China') characters, lengthb ('China') bytes from dual

Instr (a ll' b) in a, find the b1select instr ('Hello World',' ll') location from dual

Lpad left fill, rpad right fill

1select lpad ('abcd', 10,' *') left, rpad ('abcd', 10,' *') right from dual

Trim removes the specified characters before and after: remove the'H' before and after.

1select trim ('H' from 'Hello WorldH') from dual

Replace replacement

1select replace ('Hello World',' l','*') from dual