ORACLE string processing and environment variable functions

I. String part1.CONCAT Connection two strings;

Sql> Select Concat (' 010-', ' 7163738 ') from dual;

CONCAT (' 010
-----------
010-7163738

 2. Length returns the lengths of the strings;

Sql> Select Length (' 12345678 ') from dual;

LENGTH (' 12345678 ')
------------------
8

3.RPAD and LpadRpad right padding character Lpad left padding character

Sql> Select Lpad (' James ', ' Ten, '-') as name from dual;

NAME
----------
-----James

4. LTrim and RTrimLTRIM Delete The string that appears to the left RTRIM delete the string that appears to the right

Sql> Select LTrim (RTrim (' JAMES ', '), ') from dual;

LTRIM
-----
JAMES

 TRIM deletes the characters at both ends, without specifying the default is a space;

Sql> SELECT Trim (' G ') from dual;

T
-
G

Sql> Select Trim (' O ' from ' Onohhooho ') from dual;

TRIM (' O
-------
Nohhooh

5.SUBSTR (String,start,count)takes a substring, starting with start, taking Countsql> Select substr (' 13088888888 ', 3,8) from dual;SUBSTR ('--------088888886.REPLACE (' string ', ' s1 ', ' s2 ')the character or variable that string wants to be replacedS1 replaced strings2 The string to replacesql> Select replace (' He love you ', ' he ', ' I ') from dual;REPLACE (' heloveyou ', ' HE ', ' I ')------------------------------I Love you7. Chartorowidconverting character data types to ROWID typessql> Select Rowid,rowidtochar (ROWID), ename from Scott.emp;ROWID Rowidtochar (ROWID) ename------------------                       ------------------ ----------aaaafkaacaaaaeqaaa aaaafkaacaaaaeqaaa SMITHAaaafkaacaaaaeqaab Aaaafkaacaaaaeqaab ALLENAAAAFKAACAAAAEQAAC AAAAFKAACAAAAEQAAC WARDAaaafkaacaaaaeqaad Aaaafkaacaaaaeqaad JONES 8.CONVERT (C,dset,sset)to convert a source string from one language character set to anothersql> Select CONVERT (' Strutz ', ' we8hp ', ' f7dec ') ' conversion ' from dual;Conver------Strutz 9.HEXTORAW and Rawtohextconverts a hexadecimal-formed string into a binaryRawtohextconverts a binary-composed string to hexadecimal  10.to_char (date, ' format ') converted to characterssql> Select To_char (sysdate, ' Yyyy/mm/dd hh24:mi:ss ') from dual;to_char (sysdate, ' YY-------------------2017/11/06 21:14:41 11.to_date (String, ' format ')Convert a string to a date in Oracle12.     To_number converts a character to a digital sql> select To_number (') ' year from dual; Year---------201713.to_multi_byteConverts single-byte characters in a string to multibyte characters, or half-width to full-width

Sql> Select To_multi_byte (' Zhang ') from dual;

To_multi_b
----------
Zhang

Greatest and LEASTgreatest returns the maximum value, which is the encoding size of the comparison character.sql> Select Greatest (' AA ', ' AB ', ' AC ') from dual;GR--ACleast returns the minimum valueTwo. Environment variable function 1. UID returns a unique integer that identifies the current user sql> show Useruser is "Hs_user"

Sql> select UID from dual;

Uid
----------
515

2. Userevnreturns information about the current user's environmententryid,sid,terminal,isdba,lable,language,client_info,lang,vsizereturns the current session IDsql>select userenv (' Sid ') from dual;USERENV (' SESSIONID ')-------------------- the LANGUAGE return to the current localesql> Select Userenv (' language ') from dual;USERENV (' LANGUAGE ')----------------------------------------------------simplified Chinese_china. ZHS16GBKLANG TERMINAL Returns the user's terminal or machine's logosql> Select Userenv (' Terminal ') from dual;USERENV (' Termina----------------OP_XJ

 

ORACLE string processing and environment variable functions