How to get 5 char on left of a field

[annanathan]

Hi All,
Does anyone how to find the left 5 characters of a field in SQL? is there a built-in function that can be used to achieve this?

Any help is appreciated.
Thank you in advance.

 

[PruSQLer]

Substr(field1, 1, 5) works in most DBMS's.

 

[karluk]

This depends on which database you're using. SQL Server has a "left" function which does what you want.

select left(char_col, 5) from your_table

I don't think Oracle has the "left" function, but you can use "substr" instead.

select substr(char_col, 1, 5) from your_table;

No doubt other databases have their own variations.

 

[annanathan]

Thank you guys... it works with substr...

What about 5 characters from the right? and
What about exactly the 5th character?

Thank you in advance.

 

[olded]

Everybody is right about substr being database vendor specific.

Generally, to get just the 5th character

substr(field1, 5, 5)

To get the 5 characters from the right you need field1 length:

substr(field1, (length(field1) - 4), (length(field1)))

The above is untested since my database is informix and it's substr definition is:

substr(field, start_postion, length)

so in my world, it's:

substr(field1, (length(field1) - 4), 5)

Regards,

Ed
Schaefer

 

[annanathan]

Thank you all for your help... I have used the substr as you have taught me and it's working like a charm.

However I have another question

Does anyone know how to find a particular character in a field (let's say a space) and substitute that with another character(let's say with nothing)?

Thank you in advance.

 

[PruSQLer]

That particular operation is very DBMS specific. For instance, in DB2 I would use the TRANSLATE function. MS Access is something else (which I can't recall anymore), Oracle probably has another one, etc, etc. Tell us what DBMS you're using and I'm sure someone here will know it.

 

[annanathan]

I'm using Oracle...

Thank you.

 

[angiole]

For Oracle, the function is called, originally enough, REPLACE.

Works as follows:

SELECT REPLACE('JACK and JUE','J','BL') "Changes"
FROM DUAL;

Changes
--------------
BLACK and BLUE

AA

 

[annanathan]

Thank you... it's working fine
Where can I find all the built-in functions (like REPLACE) in Oracle?

 

[tlbroadbent]

If you have a number of Oracle questions, you should post them in an Oracle forum - forum186 (Oracle 8 and 8i) or forum759 (Oracle 9i). Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.