-
Hi Friends..
I have a column much like this one :-
company_name
--------------------
joe bloggs T/A a company name
joe bloggs the 2nd T/A another company name
I need the sql that would split this column so that it looks like this through sqlplus :-
company_name trading_as
------------------ -----------------------
joe bloggs a company name
joe bloggs the 2nd another company name
Any help with this would be much apreciated.
Thanks
Suresh
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
-
Use the instr, substr, and strlen functions.
Find the string T/A with instr.
Then substr from 1 to the beginning of T/A to get the first column
Then substr from T/A to the string length to get the second column.
Good luck.
David Knight
OCP DBA 8i, 9i, 10g
-
Hi..
Does this mean that I'll have to do this through a pl/sql block ?
Is there not a way using just sql ?
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
-
It is just SQL*Plus.
Do you have documentation? If not, i can post some sample code.
David Knight
OCP DBA 8i, 9i, 10g
-
This is quick and dirty (not tested), but the basic idea is okay.
select substr(company_name, 1, instr(company_name, 'T/A'),
substr(instr(company_name, 'T/A') + 2, strlen(company_name))
from table_name;
Hope that helps.
David Knight
OCP DBA 8i, 9i, 10g
-
Hi David..
you're the daddy !!
modified it a little bit but works like a dream!!!
Thanks for your help
Suresh
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
-
Honestly Suresh you need not work so hard.
This will do
select replace (COMPANY_NAME_TRADING_AS ,'T/A','')
from COMPANY
A farmer learns more from a bad harvest than a good one
-
The output will be one column and not two.
David Knight
OCP DBA 8i, 9i, 10g
-
Oh! Sorry, I was a bit too quick in answering that one!
Suresh's example output (copied below) gave me a wrong impression that the output was needed in one column.
----
need the sql that would split this column so that it looks like this through sqlplus :-
company_name trading_as
------------------ -----------------------
joe bloggs a company name
joe bloggs the 2nd another company name
----
A farmer learns more from a bad harvest than a good one
-
Hi David..
one point..
I have never heard of or able to use strlen function
I used length(column_name) instead.
is strlen a function ?
I have sqlplus r8.1.7
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|