Home » SQL & PL/SQL » SQL & PL/SQL » To retrieve first characters before period
To retrieve first characters before period [message #306596] Fri, 14 March 2008 19:55 Go to next message
cores
Messages: 15
Registered: March 2008
Junior Member
I have project_name field:

1. Technology
2a. Literature
5c. Math

I need to have results:

1
2a
5c

What functions I need to use to retrieve all characters before period?

Thank you!
Re: To retrieve first characters before period [message #306598 is a reply to message #306596] Fri, 14 March 2008 20:19 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check out INSTR and SUBSTR.
Re: To retrieve first characters before period [message #306599 is a reply to message #306596] Fri, 14 March 2008 20:25 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Since you are on 10g, you can also consider
SQL> with t as (
  2  select '1. Technology' project_name from dual union all
  3  select '2a. Literature' from dual union all
  4  select '5c. Math' from dual)
  5  --
  6  select project_name
  7     , regexp_substr(project_name, '^[^.]*') sub from t;

PROJECT_NAME   SUB
-------------- --------------
1. Technology  1
2a. Literature 2a
5c. Math       5c
Re: To retrieve first characters before period [message #306611 is a reply to message #306599] Sat, 15 March 2008 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is nice but I should not consider regexp against substr and instr in such simple case for performances reason (and maybe readability(?) one but it is a question of practice).

Regards
Michel
Re: To retrieve first characters before period [message #306662 is a reply to message #306596] Sat, 15 March 2008 11:00 Go to previous messageGo to next message
cores
Messages: 15
Registered: March 2008
Junior Member
Thanks to all for reply.


In Crystal Reports I would write:

Left({project_name}, InStr({project_name}, '.') -1)
OR
Split({project_name},".")[1]


Is there something similar in Oracle to retrieve only first characters before period?

I don't know exactly the project names of the client. That is why I am afraid I cannot use reg_exp. Anyway, I am new in Oracle.

Thanks
Re: To retrieve first characters before period [message #306663 is a reply to message #306662] Sat, 15 March 2008 11:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9099
Registered: November 2002
Location: California, USA
Senior Member
This can be done very simply using the Oracle supplied SUBSTR and INSTR functions, as originally suggested by ebrian. If you click on those words in his post, it will take you directly to the sections of the Oracle online documentation that provide explanation, syntax, and examples for those functions. As a newbie, we try to point you in the right direction, rather than do everything for you, and expect you to make some attempt of your own. If, after reading those sections, you still need help, then post a copy and paste of what you tried, and the results or error that you got. Please read the forum guidelines for more information on what we expect.
Re: To retrieve first characters before period [message #306664 is a reply to message #306611] Sat, 15 March 2008 11:23 Go to previous messageGo to next message
cores
Messages: 15
Registered: March 2008
Junior Member
OK, thanks again to you!

I have received results I wanted:

with t as (
select project_name from projects )
select project_name, regexp_substr(project_name, '^[^.]*') sub from t

But how can I insert it in SELECT statement of the query with many joins?

Thanks
Re: To retrieve first characters before period [message #306665 is a reply to message #306664] Sat, 15 March 2008 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Be studying SQL Reference.
As Barbara said we can help you but we don't just give you solutions.
You chose to keep the (good) query Brian gave and not study the other solution (nor this one).
You seem to be a lazy one.

Regards
Michel

[Updated on: Sat, 15 March 2008 11:30]

Report message to a moderator

Re: To retrieve first characters before period [message #306668 is a reply to message #306665] Sat, 15 March 2008 11:43 Go to previous messageGo to next message
cores
Messages: 15
Registered: March 2008
Junior Member
Ok.

I'll keep working on it.
Re: To retrieve first characters before period [message #306677 is a reply to message #306665] Sat, 15 March 2008 12:59 Go to previous messageGo to next message
cores
Messages: 15
Registered: March 2008
Junior Member
Thanks to all,

It works good:

select regexp_substr(project_name, '^[^.]*') from projects;

Michel, you mentioned re performance issue.

Do you think I need to look at Instr and Substr functions to use. Just, it is very urgent and I need to finish it as soon as possible. Or can I go with this solution?
Re: To retrieve first characters before period [message #306680 is a reply to message #306677] Sat, 15 March 2008 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, I prefer to use substr and instr in this simple case.
The syntax is so close to the one you posted for Crystal Report than it will took you a couple of minutes to write it.

Regards
Michel
Re: To retrieve first characters before period [message #306681 is a reply to message #306677] Sat, 15 March 2008 13:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9099
Registered: November 2002
Location: California, USA
Senior Member
I guess you can refer some newbies to the documentation, but you can't make them read it, and they think the only ones worth listening to are those that spoonfeed them, so here:

select substr (project_name, 1, instr (project_name, '.') - 1) from projects;

Test and compare for yourself and let us know.
Re: To retrieve first characters before period [message #306683 is a reply to message #306681] Sat, 15 March 2008 13:42 Go to previous message
cores
Messages: 15
Registered: March 2008
Junior Member
Barbara, it works well.

To be honest, it is easier for me to understand regexp_substr syntax.

Thanks again.


Previous Topic: character set name is not recognized
Next Topic: How to use dll commands using database triggers
Goto Forum:
  


Current Time: Tue Nov 05 13:36:06 CST 2024