Oracle character functions

Source: Internet
Author: User

1, String intercept
Select substr (' abcdef ', 1,3) from dual

2, finding the substring location
Select InStr (' Abcfdgfdhd ', ' FD ') from dual

3, String connection
Select ' HELLO ' | | ' Hello world ' from dual;

4, 1) Remove spaces from the string
Select LTrim (' abc ') s1,
RTrim (' Zhang ') s2,
Trim (' Zhang ') S3 from dual

2) Remove the preamble and suffix
Select Trim (leading 9 from 9998767999) S1,
Trim (trailing 9 from 9998767999) S2,
Trim (9 from 9998767999) S3 from dual;

5, returns the ASCII value of the first letter of the string
Select ASCII (' a ') from dual

6, returns the letter corresponding to the ASCII value
Select CHR from dual

7, calculating the string length
Select Length (' abcdef ') from dual

8,initcap (capitalized), lower (lowercase), upper (uppercase)
Select lower (' ABC ') s1,
Upper (' def ') S2,
Initcap (' EFG ') S3
from dual;

9,replace
Select replace (' abc ', ' B ', ' XY ') from dual;

10,translate
Select Translate (' abc ', ' B ', ' xx ') from dual; --X is 1 bits

One by one, decode[implement if. Then logic] Note: The first is an expression, and the last is a value that does not meet any one of the criteria
Select Deptno,decode (deptno,10, ' 1 ', 20, ' 2 ', 30, ' 3 ', ' other ') from dept;
Cases:
Select Seed,account_name,decode (seed,111,1000,200,2000,0) from t_userinfo//if Seed is 111, take 1000; 200, fetch 2000; other FETCH 0
Select Seed,account_name,decode (seed-111), 1, ' Big Seed ', -1, ' Little seed ', ' equal Seed ') from t_userinfo//if seed >111, the display is large; 200, the display is small; others are displayed equal

case[implementation of switch. Case Logic]
SELECT Case X-field
When X-field < Max then ' X-field less than 40 '
When X-field < ' X-field less than 50 '
When X-field < ' X-field less than 60 '
ELSE ' Unbeknown '
END
From DUAL

Note: Case statements are very flexible when dealing with similar problems. Decode is more concise when only a small number of values need to be matched

The original address; http://plat.delit.cn/thread-191-1-1.html

Reprint please indicate the source;

Author: metric Technology www. Delit. cn

Oracle character functions

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.