Home » SQL & PL/SQL » SQL & PL/SQL » Can this be possible through sql ? (10.2.0.2)
Can this be possible through sql ? [message #424264] Thu, 01 October 2009 03:54 Go to next message
bryanadams
Messages: 9
Registered: September 2009
Location: Bangalore
Junior Member
Hi All,


[b]Username[/b]   [b]Full Name [/b]
15179SE	         KULKARNI, Mr. SATISH C.
UDP2376	         PANDIT, Mr. ULHAS D.
JSI4391	         INAMDAR, Mr. JAINUDDIN S.
SAJ8332	         JOSHI, Mr. SHRINIWAS A.
RDN10163ME	Nandkhile, Mr. R. D. (CORP)
UGJ7894SE	JOSHI, Mr. UDAY GAJANAN
8663AOSE	Kalra, Mr. Jaspalsingh (SE)


out of these username and full name .. in username i should extract the numbers and in full name i should extract only the initials. Can this be possible ..

Regards

Bryanadams

[Updated on: Thu, 01 October 2009 03:59]

Report message to a moderator

Re: Can this be possible through sql ? [message #424266 is a reply to message #424264] Thu, 01 October 2009 04:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you show us the exact results that you want to see from this test data.
Re: Can this be possible through sql ? [message #424269 is a reply to message #424264] Thu, 01 October 2009 04:05 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes, it is possible.

regards,
Delna
Re: Can this be possible through sql ? [message #424270 is a reply to message #424269] Thu, 01 October 2009 04:06 Go to previous messageGo to next message
bryanadams
Messages: 9
Registered: September 2009
Location: Bangalore
Junior Member

Hi Delna,

Can you please help me out in this please .. i am feeling a bit tough with regular expressions.

Regards

Bala
Re: Can this be possible through sql ? [message #424271 is a reply to message #424270] Thu, 01 October 2009 04:10 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
Can you show us the exact results that you want to see from this test data.


with required CREATE and INSERT statements?

regards,
Delna
Re: Can this be possible through sql ? [message #424273 is a reply to message #424266] Thu, 01 October 2009 04:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
May I enquire politely why you didn't bother including the details I asked for?
If you don't want my help, then I'm quite happy with that.

To clarify my request: Your question is imprecise. when you say that you want 'only the initials' from the name 'INAMDAR, Mr. JAINUDDIN S.' do you want:

1) I J S.
2) I J S
3) I. J. S.
4) S.
5) S
6) J S
7) J. S.

I can give you a justification for each of these being the answer to your question.
So, to repeat - please provide us with the expected results from your test data.
Re: Can this be possible through sql ? [message #424274 is a reply to message #424271] Thu, 01 October 2009 04:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
with required CREATE and INSERT statements?
Unlike some of the members round here, I don't mind spending 30 seconds converting the data posted into a set of insert statements.
create table test_074 (username  varchar2(20), full_name  varchar2(50));

insert into test_074 values ('15179SE','KULKARNI, Mr. SATISH C.');
insert into test_074 values ('UDP2376','PANDIT, Mr. ULHAS D.');
insert into test_074 values ('JSI4391','INAMDAR, Mr. JAINUDDIN S.');
insert into test_074 values ('SAJ8332','JOSHI, Mr. SHRINIWAS A.');
insert into test_074 values ('RDN10163ME','Nandkhile, Mr. R. D. (CORP)');
insert into test_074 values ('UGJ7894SE','JOSHI, Mr. UDAY GAJANAN');
insert into test_074 values ('8663AOSE','Kalra, Mr. Jaspalsingh (SE)');

Re: Can this be possible through sql ? [message #424276 is a reply to message #424271] Thu, 01 October 2009 04:18 Go to previous messageGo to next message
bryanadams
Messages: 9
Registered: September 2009
Location: Bangalore
Junior Member

Hi,

create table users(username varchar2(20),fullname varchar2(100));

insert into users('15179SE','KULKARNI, Mr. SATISH C.');
insert into users('UDP2376','PANDIT, Mr. ULHAS D.');
insert into users('JSI4391','INAMDAR, Mr. JAINUDDIN S.');
insert into users('RDN10163ME','Nandkhile, Mr. R. D. (CORP)');
insert into users('UGJ7894SE','JOSHI, Mr. UDAY GAJANAN');
insert into users('8663AOSE','Kalra, Mr. Jaspalsingh (SE)');

Output Expected:-
-----------------

sck15179
udp2376
jsi4391
saj8332
rdn10163
ugj7894
jk8663

Regards

Bryan
Re: Can this be possible through sql ? [message #424278 is a reply to message #424274] Thu, 01 October 2009 04:19 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello JRowBottom sir,

I am not alone in this group. Razz
And this labour and combersome takes many more seconds in my case. Sad

regards,
Delna
Re: Can this be possible through sql ? [message #424283 is a reply to message #424278] Thu, 01 October 2009 04:45 Go to previous messageGo to next message
bryanadams
Messages: 9
Registered: September 2009
Location: Bangalore
Junior Member
Hi Delna/JRowbottom


Can someone help me out please .. as i need this really urgent.

Regards

Bryan.
Re: Can this be possible through sql ? [message #424284 is a reply to message #424283] Thu, 01 October 2009 04:56 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Following is just clue, not perfact solution.

SQL>r
  1  with usr as
  2     (select
  3     regexp_replace(username,'[A-Z,a-z]') expr1,
  4     regexp_substr(fullname,'^[A-Z,a-z]*, Mr.') expr2,
  5     initcap(regexp_replace(fullname,'^[A-Z,a-z]*, Mr.')) expr3 from users)
  6  select expr1,
  7  regexp_replace(expr3,'[^A-Z]') expr4,
  8* substr(expr2,1,1) expr5 from usr

EXPR1                EXPR4                                                                                                E
-------------------- -------------------------------------------------------------------------------
15179                SC                                                                                                   K
2376                 UD                                                                                                   P
4391                 JS                                                                                                   I
10163                RDC                                                                                                  N
7894                 UG                                                                                                   J
8663                 JS                                                                                                   K

6 rows selected.


regards,
Delna
Re: Can this be possible through sql ? [message #424289 is a reply to message #424276] Thu, 01 October 2009 05:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just because lots of people do something doesn't mean it's the best approach...

There as to be a better way to do this....
create table test_074 (username  varchar2(20), full_name  varchar2(50));

insert into test_074 values ('15179SE','KULKARNI, Mr. SATISH C.');
insert into test_074 values ('UDP2376','PANDIT, Mr. ULHAS D.');
insert into test_074 values ('JSI4391','INAMDAR, Mr. JAINUDDIN S.');
insert into test_074 values ('SAJ8332','JOSHI, Mr. SHRINIWAS A.');
insert into test_074 values ('RDN10163ME','Nandkhile, Mr. R. D. (CORP)');
insert into test_074 values ('UGJ7894SE','JOSHI, Mr. UDAY GAJANAN');
insert into test_074 values ('8663AOSE','Kalra, Mr. Jaspalsingh (SE)');
  
with src as 
(select regexp_substr(username,'[0-9]+') uname
      ,regexp_replace(full_name,'(\([^)]+\)|Mr\.|,|\.|[a-z]+)','') fname
from test_074)
select regexp_replace(regexp_replace(fname,'([A-Z])([A-Z]*[ ]*)','\1'),'([A-Z])([A-Z])([A-Z])','\2\3\1')||uname
from src;
Re: Can this be possible through sql ? [message #424291 is a reply to message #424289] Thu, 01 October 2009 05:24 Go to previous messageGo to next message
bryanadams
Messages: 9
Registered: September 2009
Location: Bangalore
Junior Member

Hi JRowbottom & Delna,

Thank you very much for your response .

JRowbottom your response seems to have more clarity in the output but i could see there is mistake in last row (i.e

insert into test_074 values ('8663AOSE','Kalra, Mr. Jaspalsingh (SE)');

Actually the output should be first name ,middle name & lastname it means the output shd be like this .. jk8663 only and in our case we getting as kj8663.



Regards

Bryan
Re: Can this be possible through sql ? [message #424292 is a reply to message #424291] Thu, 01 October 2009 05:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
True - it's not matching the regexp as there are only two letters.

Try this:
with src as 
(select regexp_substr(username,'[0-9]+') uname
      ,regexp_replace(full_name,'(\([^)]+\)|Mr\.|,|\.|[a-z]+)','') fname
from test_074)
select regexp_replace(regexp_replace(fname,'([A-Z])([A-Z]*[ ]*)','\1'),'([A-Z])([A-Z])([A-Z]*)','\2\3\1')
from src;
Re: Can this be possible through sql ? [message #424304 is a reply to message #424276] Thu, 01 October 2009 07:27 Go to previous messageGo to next message
KAPILAGG29
Messages: 3
Registered: September 2009
Junior Member

HELLO

THIS IS QUERY WHICH SERVE your PURPOSE

select lower(SUBSTR(regexp_substr(FULL_NAME,'([A-Z])',
INSTR(FULL_NAME,' ',1,2)),1))
||lower(SUBSTR(regexp_substr(FULL_NAME,'(. +[A-Z])',
INSTR(FULL_NAME,' ',1,2)),3))||lower(substr(full_name,1,1))||REGEXP_SUBSTR(USERNAME,'([0-9]){3,6}') ID from test_074

[Updated on: Thu, 01 October 2009 08:41] by Moderator

Report message to a moderator

Re: Can this be possible through sql ? [message #424306 is a reply to message #424304] Thu, 01 October 2009 08:43 Go to previous message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum,

Please read OraFAQ Forum Guide.
In particular, Posting guidelines and "How to format your post?" sections.

Regards
Michel
Previous Topic: problem in analytic query
Next Topic: procedure fails due to role
Goto Forum:
  


Current Time: Wed Sep 28 16:01:31 CDT 2016

Total time taken to generate the page: 0.10585 seconds