How to fetch 4 characters from a string in oracle?

Question

By using following query i am able to fetch a particular string.Now this output string contains alphanumeric characters.e.g.abcd123,pqrs542356. Now i want to fetch only first 4 characters of this string which will be always alpha bates.

Query::

(SELECT SUBSTR(in_msg, INSTR( in_msg,'?', 1, 10 )+ 1, INSTR(in_msg,'?', 1, 11 ) - INSTR( in_msg,'?', 1, 10 )- 1)
 FROM emp_message 
WHERE emp_no IN (SELECT emp_no 
                   FROM main_table 
                  WHERE name like '%abcd%')

This query returns output as e.g.abcd1234,pqrs145423. Again i want to fetch only first 4 characters from this query output. Can somebody help me in this.

score 4 · Answer 1 · answered Dec 21 '10 at 06:02

4

You can use substr (like you already do):

SUBSTR(value, 1, 4)

answered Dec 21 '10 at 06:02

GolezTrol

114,394
18
182
210

score 0 · Answer 2 · answered Dec 21 '10 at 06:00

0

Here it is:

SUBSTR(field, 0, 4)

answered Dec 21 '10 at 06:00

zerkms

249,484
69
436
539

@Thilo: oops, it is not obvious though :-S – zerkms Dec 21 '10 at 06:02
0 is treated as 1, and negative indexes count from the right side: http://www.adp-gmbh.ch/ora/sql/substr.html – Thilo Dec 21 '10 at 06:05
@Thilo: i know about negatives. I meant that all programming languages count char position from 0. – zerkms Dec 21 '10 at 06:07
2

Not all ;-) http://stackoverflow.com/questions/1499749/list-of-1-indexed-programming-languages – Thilo Dec 21 '10 at 06:22

score 0 · Answer 3 · answered Dec 21 '10 at 06:00

0

select substr(x, 1, 4) from
   ( select x from .....  )

answered Dec 21 '10 at 06:00

Thilo

257,207
101
511
656

How to fetch 4 characters from a string in oracle?

3 Answers3