Split string substr in SQL and statistical character occurrences frequency Replace usage example explanation

The split string is a number of lines

Example one: Require that ' king ' in the table emp be split into four lines per line of words

Note: substr (str,pos): intercepts the character at the beginning of POS position;

SUBSTR (Str,pos,len): Selects the next Len character from the POS position

Table EMP:

Eid	Ename
1	Ring
2	King
3	Ting
4	Ping

First, set up table tmp (skeleton table, in order to match table one use):

Tid

1

2

3

4

5

Then, select Emp.ename,tmp.tid from emp,tmp where emp.ename= ' king '; */* No primary key and foreign key, generate King's Cartesian set/

Ename TID

King 1
King 2
King 3
King 4
King 5
Last: Select substr (ename,tid,1) as a from (select Emp.ename,tmp.tid from emp,tmp where Emp.ename= ' King ') as E wher E e.tid <= Length (e.ename);

Results:

A:

K

I

N

G

If select substr (Ename,tid) as B, substr (Ename,length (ename)-tid+1) .... The results will appear

b C

King G

ing ng

ng ing

G King

Second, count the number of occurrences of a character in a string

Replace function: replace (string expression to search for, string to find, replacement string)

Example two: Calculating the number of commas in 10,clark,manager

Select (length (' 10,clark,manager ')-length (replace (' 10,clark,manager ', ', ', '))/length (', ')) asC from TableName;

Split string substr in SQL and statistical character occurrences frequency Replace usage example explanation