Home » SQL & PL/SQL » SQL & PL/SQL » Char search
Char search [message #206937] Sat, 02 December 2006 09:30 Go to next message
rnallamo
Messages: 24
Registered: August 2005
Junior Member
Hi need some help

how to search for a char(-) in a string.
i have data coming as below
ILLINIOS-IL
INDIANA-IN
MICHIGAN-MI
OHIO-OH
WISCONSIN-WI
FLORIDA-FL
NORHT CAROLINA-NC
SOUTH CAROLINA-SC
TEXAS - TX
i need to get just the last 2 chars in the select.
out should be similar to this
IL
IN
MI
OH
WI
FL
NC
SC
TX
any help is appreciated
Re: Char search [message #206938 is a reply to message #206937] Sat, 02 December 2006 09:32 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
to search (-), you can use instr.

But as you need to get the last two characters, you can just use substr alone.(from back wards)

By
Vamsi
Re: Char search [message #206939 is a reply to message #206937] Sat, 02 December 2006 09:37 Go to previous messageGo to next message
rnallamo
Messages: 24
Registered: August 2005
Junior Member
yep got it, thnx

substr(statename, instr(statename, '-')+1)
Re: Char search [message #206940 is a reply to message #206939] Sat, 02 December 2006 09:51 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
substr(statename,-2)

By
Vamsi
Re: Char search [message #206941 is a reply to message #206937] Sat, 02 December 2006 09:53 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you always want the last two characters, you can use SUBSTR by itself:

SQL> select * from states;

STATE_NAME
------------------------------
ILLINOIS-IL
INDIANA-IN
MICHIGAN-MI
OHIO-OH
WISCONSIN-WI
FLORIDA-FL
NORTH CAROLINA-NC
SOUTH CAROLINA-SC
TEXAS-TX

9 rows selected.

SQL> select substr(state_name,-2) from states;

SU
--
IL
IN
MI
OH
WI
FL
NC
SC
TX

9 rows selected.

Sorry vamsi, I was a little slow to the punch.

[Updated on: Sat, 02 December 2006 10:04]

Report message to a moderator

Previous Topic: How to get the second highest salary from a table
Next Topic: computing and inserting values into table??
Goto Forum:
  


Current Time: Wed Dec 07 22:12:01 CST 2016

Total time taken to generate the page: 0.14334 seconds