To retrieve first characters before period [message #306596] |
Fri, 14 March 2008 19:55 |
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 #306662 is a reply to message #306596] |
Sat, 15 March 2008 11:00 |
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 |
|
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 |
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 #306677 is a reply to message #306665] |
Sat, 15 March 2008 12:59 |
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?
|
|
|
|
|
|