Shulou(Shulou.com)06/01 Report--

1: how to determine the value of a field: which data contains lowercase letters or upper and lower case letters

A record of lowercase letters in the value of the judgment field NAME

Method 1:

SELECT NAME FROM TEST WHERE regexp_like (NAME,' [[: lower:]]')

Mode 2

SELECT NAME FROM TEST WHERE regexp_like (NAME,' [a murz]')

A record of uppercase letters in the value of the judgment field NAME

Method 1:

SELECT NAME FROM TEST WHERE regexp_like (NAME,' [[: upper:]]')

Mode 2:

SELECT NAME FROM TEST WHERE regexp_like (NAME,' [Amurz]')

2: how to determine that the value in the field contains special characters

For example, I want to find records that contain the special character & in the field NAME of table TEST

SELECT NAME FROM ESCMOWNER.TEST WHERE regexp_like (NAME,' [&]')

3: how to determine whether there is a space before or after the value in the field

1: record information with spaces in front of or after the field:

SELECT * FROM TEST WHERE length (NAME) > length (trim (NAME))

2: record information with a space before the field value:

SELECT * FROM ESCMOWNER.TEST WHERE LENGTH (LTRIM (NAME)) < LENGTH (NAME)

3: when there is a space after the field value:

SELECT * FROM ESCMOWNER.TEST WHERE LENGTH (RTRIM (NAME)) < LENGTH (NAME)

4: how to determine that the value in the field contains spaces

It has always been a very difficult and troublesome thing to think that a field contains spaces (spaces are located anywhere), but in front of ORACLE's regular expression function REGEXP_LIKE, everything is fine.

SELECT * FROM TEST WHERE REGEXP_LIKE (NAME,'() +')

Add: let's take a look at the single table query statement of Oracle

Test forms that use the Oracle database:

-- single table query data syntax select (query) * all / query fields, multiple fields separated by from (from) table names-query all employees' information select * from scott.emp;select * from dept;-- query all employees' numbers, names and position specified field names look up data select empno,ename,job from emp;-- query all employees' numbers, names and annual salaries select * from emp Select empno,ename,sal*12 from emp;-- does not modify the original data by using the operator through the query-- it just performs the operation at the time of the query, showing a new data table, select empno,ename,sal*12 from emp;-- calculation. A virtual table is provided. Dual-- dual is a virtual table, designed for testing use-- aliases can be given to fields, select 1, AS result1 from dual;select sysdate AS DATES from dual;--, aliases sal*12 to income as can be omitted--''single quotes represent strings "" aliases, if aliases contain special characters, use "" wrap select empno,ename,sal*12 "annual salary" from emp. -- query the names, positions and salaries of all users, and display them as follows-- name: xxx, position: xxx, salary: xxx-- string concatenation use | | connection is not + select 'name:' | | ename |, position:'| | job |', salary:'| | sal info from emp;-- wants to display all positions, so there can be no repetition excluding repetition-- to reuse distinctselect distinct job,ename from emp. -- sort ascending and descending (asc desc)-- sort the fields that need to be specified by default is ascending order asc-- can sort numeric date string types select * from emp order by job asc -- descending by salary, and then ranking older employees by descending date if the salary is the same-- sort by multiple fields. Order by only needs to write once, and subsequent sorting fields are used, separated by select empno,ename,hiredate,sal from emp order by sal desc, hiredate desc;-- query all employees' numbers, names, annual salaries, sort by annual salary-- sort by alias select empno,ename,sal*12 income from emp order by income desc.

Summary

The above are some special query sentences about the table in ORACLE introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.