How do I get a subset of a string, up to a certain character? 3

[ksnpc]

Hi,

I'm trying to write a sql query in ToRA connected to an Oracle9i database. I have a string field that looks like this - "142.000000 - 34527". The first part of the field is the same value as the primary key of another table (142). I would like to get just the first part of the field up to the '.' so I can use it to join to the other table. I can't just go for the first three characters because the first number can be up to 4 digits long. Is there a command that will get the part of a string up to a special character?

Thanks,
Shauna

 

[lewisp]

substr(<string>,1,Instr(<string>,'.') - 1)

 

[DotNetGnat]

you need to INSTR() and SUBSTRING() functions...

-DNG

 

[SantaMufasa]

(BTW, how do you pronounce "ksnpc"?)

Here is some code for you. (Focus just on the SELECT statement since the other code is there just to set up a proof of concept since I don't have your data for testing.):

var str_in char(20)
exec :str_in := '142.000000 - 34527'
col a heading "Extracted|Value" format 9999
select substr(:str_in,1,instr(:str_in,'.')-1) a from dual;

Extracted
Value
---------
142

Let us know if this gets you on your way.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Whoa...a three-way duel, and Lewis was the quickest draw.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ksnpc]

Wow, that was quick! And it took care of my problem.

PS - ksnpc is just the abbreviation of where I work so not really pronouncable (or creative).

Thanks to all!

 

[Turkbear]

Hi,
I thought is was a description of what my country cousins do on a musical Saturday night :

Kiss 'N Pick


Sorry, couldn't resist...Its a Friday before a long weekend...









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

I'm sure the folks at "Kentucky State Nature Preserves Commission" will love to know how much more fun your country cousins are having with KSNPC than they have at work. (Of course, who knows, the KSNPC folks may be doing a li'l Kissin' 'n' Pickin', too.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ksnpc]

I think I'll suggest that at the next staff meeting.