Home » SQL & PL/SQL » SQL & PL/SQL » SQL Help (10g)
icon5.gif  SQL Help [message #619472] Tue, 22 July 2014 16:39 Go to next message
jimbojames
Messages: 2
Registered: July 2014
Location: england
Junior Member
Hi all please can someone help me as my SQL isnt great Smile

In our database we have a field and its formatted in a strange way, (Im dont know why is was entered like this)
but it looks like this: 10:1-250:US17, what i need is the numbers before the first :, the problem is it could be up to2 digits

example record 1 :10:1-250:US17
example record 2 :5:1-250:US17

there there a way in oracle sql to look for the first : the return what ever is to the left of the first :

hope this makes sence

Many thanks for anynoe taking the time to help me with my query???
Embarassed

[Updated on: Tue, 22 July 2014 16:44]

Report message to a moderator

Re: SQL Help [message #619477 is a reply to message #619472] Tue, 22 July 2014 17:04 Go to previous messageGo to next message
superboy87
Messages: 3
Registered: May 2014
Junior Member
SELECT SUBSTR(recordName, 0, INSTR(recordName, ':')-1) AS output
FROM TableName

[Updated on: Tue, 22 July 2014 17:04]

Report message to a moderator

Re: SQL Help [message #619478 is a reply to message #619472] Tue, 22 July 2014 17:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: SQL Help [message #619497 is a reply to message #619478] Wed, 23 July 2014 01:14 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Alternatively,
select regexp_substr (column_name, '^\d+')
from your_table
Re: SQL Help [message #619513 is a reply to message #619497] Wed, 23 July 2014 02:22 Go to previous messageGo to next message
jimbojames
Messages: 2
Registered: July 2014
Location: england
Junior Member
Great!!!!! this is fantastic........ both suggestions worked perfectly. I greatly appreciate you taking the time to help me out Smile....
Re: SQL Help [message #619517 is a reply to message #619477] Wed, 23 July 2014 02:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
superboy87 wrote on Wed, 23 July 2014 03:34
SELECT SUBSTR(recordName, 0, INSTR(recordName, ':')-1) AS output
FROM TableName


That would not ONLY return DIGITS, but anything it finds before the first occurence of ":".

SQL> WITH DATA as(
  2  SELECT '10:1-250:US17' str FROM dual UNION ALL
  3  SELECT '5:1-250:US17' str FROM dual UNION ALL
  4  SELECT ':1-250:US17' str FROM dual UNION ALL
  5  SELECT 'A:1-250:US17' str FROM dual)
  6  SELECT substr(str, 0, instr(str, ':') - 1) str FROM data;

STR
-------------
10
5

A
Re: SQL Help [message #619528 is a reply to message #619517] Wed, 23 July 2014 03:38 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe the following will help:

Quote:
what i need is the numbers before the first :

Previous Topic: Can someone tell me How to debugg the procedure in the package without using any tool toad or pl sql
Next Topic: How to implement the below code in the PL/SQL?
Goto Forum:
  


Current Time: Thu Mar 28 12:14:55 CDT 2024