Home » SQL & PL/SQL » SQL & PL/SQL » How to split the serial number (oracle 10 g)
How to split the serial number [message #337477] Thu, 31 July 2008 01:02 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi all,
I have a column name serial number in my product master,for ex...the column name serial number has the data like
123rfb687rf---which i need to split the data with '.' dot symbol.like 123r.fb687.rf....can anyone help pls...

regards,
hammer
Re: How to split the serial number [message #337481 is a reply to message #337477] Thu, 31 July 2008 01:08 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
What is the logic behind the placement of the dots?
Re: How to split the serial number [message #337482 is a reply to message #337477] Thu, 31 July 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where do you want to split?

Also, always post your Oracle version (4 decimals).
Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: How to split the serial number [message #337597 is a reply to message #337482] Thu, 31 July 2008 06:50 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Michel,
Thanks for your fast response,we have some data in excel sheet, in which there is a column name called serial number.But our customers entered the serial number of the product as Z123789045YH in OLTP systems.But in my reporting end i need to see the serial number as-->Z1237.890.45YH . How would i implement this?


Thanks and Regards,
Hammer.

[Updated on: Thu, 31 July 2008 06:53]

Report message to a moderator

Re: How to split the serial number [message #337599 is a reply to message #337597] Thu, 31 July 2008 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The question remains the same: where do you put the dot?

And also, post a test as requested.

Regards
Michel
Re: How to split the serial number [message #337603 is a reply to message #337597] Thu, 31 July 2008 06:59 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

But how will you decide, where to put . in the string (serial number) entered by your users ??

and if the user has already entered the serial number in the format required by you i.e. "." (dot) separated, then what?


Regards,
Dipali..
Re: How to split the serial number [message #337615 is a reply to message #337477] Thu, 31 July 2008 07:21 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Good Evening to all,

-->This will helps you out what preceisly i need.

-->All of my product serial numbers in the excel sheet is in the form of Z123789045YH (totally 12 characters).

-->First (5 digit)1 to 5 (i.e)Z1237--indicates my product code,Next three digit indicates my product number (i.e)890,last four is my ref code.

-->I have done with one function which is as follows,(it works fine)
-----------------------------------------------------------
create or replace function func_test(v_pid nvarchar2(250))
return nvarchar2
is
v_output nvarchar2(250);
begin
v_output:=substr(v_pid,1,5)||'.'||substr(v_pid,6,3)||'.'||substr(v_pid,9,4);
return v_output;
end;
-----------------------------------------------------------
-->Is there anyother way to implement the above step..
Urgent pls....


Thanks and Regards,
Hammer.

[Updated on: Thu, 31 July 2008 07:28]

Report message to a moderator

Re: How to split the serial number [message #337617 is a reply to message #337615] Thu, 31 July 2008 07:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why is it urgent? You've got a working solution.
The only thing I might change is to simplify the code a little (I don't think you've ever actually compilled the code you showed, but we'll skip over that):
create or replace function func_test(v_pid nvarchar2)
return nvarchar2 is
begin
return substr(v_pid,1,5)||'.'||substr(v_pid,6,3)||'.'||substr(v_pid,9,4);
end;
Re: How to split the serial number [message #337620 is a reply to message #337477] Thu, 31 July 2008 07:42 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> -->Is there anyother way to implement the above step..
In function? Why? Does not it seem quite easy to you?
Of course you may forget the function and use that expression directly in SQL (which shall be faster).
Re: How to split the serial number [message #337621 is a reply to message #337617] Thu, 31 July 2008 07:42 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi JRowbottom,
Thanks a lot..Wonderful ...But the function which i wrote is also working fine...But you made it simple...I thought that there might be some other method which didn't spark to me..

Once again i would like to thank each and everyone for helping me on this..

I have one more doubt,I have one more column name Text_File,in which the data is like(190.HAPPY.ENT) all the data in the column is same as the above(190.HAPPY.ENT) only the first three digit number will change.I would like to seperate first 3 numbers in one column(190),HAPPY in other column,and ENT in other. Can anyone helpme out in this by writing a function.


Thanks and Regards,
Hammer.
Smile

[Updated on: Thu, 31 July 2008 07:49]

Report message to a moderator

Re: How to split the serial number [message #337625 is a reply to message #337621] Thu, 31 July 2008 07:54 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Have idea for another way from following demo..

17:50:06 SQL> select * from x;

A         |B         |         C
----------|----------|----------
          |20        |
a         |ab        |         0

Elapsed: 00:00:00.00
17:58:56 SQL> update x set b=substr(b,1,1) || '.' || substr(b,2,1) || '.' ;

2 rows updated.

Elapsed: 00:00:00.00
18:00:28 SQL> select * from x;

A         |B         |         C
----------|----------|----------
          |2.0.      |
a         |a.b.      |         0


Cheers..
Dipali..
Re: How to split the serial number [message #337626 is a reply to message #337621] Thu, 31 July 2008 07:55 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Dear,

Both of your problems can be easily solved using proper use of substr and instr function in just one query...

Try it..

Regards,
Dipali..

[Updated on: Thu, 31 July 2008 07:56]

Report message to a moderator

Re: How to split the serial number [message #337636 is a reply to message #337626] Thu, 31 July 2008 08:19 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Dipali,
I can split the first 3 numbers by using
substr('190.happpy.ent',1,instr('190.happy.ent','.',1,1)-1) and the last one (ent),but i would like to split the middle one (happy).

-->But i should not mention in this format
substr('190.happy.ent',5,5) to extract the middle characters,because the middle character will vary in future.

Thanks and Regards,
Hammer.
Re: How to split the serial number [message #337639 is a reply to message #337621] Thu, 31 July 2008 08:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
aviva4500 wrote on Thu, 31 July 2008 14:42
Hi JRowbottom,
Thanks a lot..Wonderful ...But the function which i wrote is also working fine...

No it does NOT.
SQL> create or replace function func_test(v_pid nvarchar2(250))
  2  return nvarchar2
  3  is
  4  v_output nvarchar2(250);
  5  begin
  6  v_output:=substr(v_pid,1,5)||'.'||substr(v_pid,6,3)||'.'||substr(v_pid,9,4);
  7  return v_output;
  8  end;
  9  /

Warning: Function created with compilation errors.

SQL> show error
Errors for FUNCTION FUNC_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/35     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

Function parameters are defined without length.
Re: How to split the serial number [message #337640 is a reply to message #337636] Thu, 31 July 2008 08:29 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Let me confirm folloiwng things about your problem first.

-->Your string will contain three pieces of inforation.
How will you separate these three pieces?
Two posibilities:
1)Each of these three pieces are separated by . (dot) or some other separator.
2)All these three pieces do have some fix size.

Let me know how do you separate the three pieces of information from your string.

Then only we can suggest you the correct path..

Regards,
Dipali..
Re: How to split the serial number [message #337644 is a reply to message #337477] Thu, 31 July 2008 08:39 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member



select substr(gateno,1,4) ||'.'||substr(gateno,5,5)||'.'||substr(gateno,9,2) from tablename;

123r.fb687.rf
Re: How to split the serial number [message #337689 is a reply to message #337615] Thu, 31 July 2008 12:36 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> var v_pid varchar2(20)
SQL> exec :v_pid := 'Z123789045YH'

PL/SQL procedure successfully completed.

SQL>
SQL> select regexp_replace(:v_pid, '(.{5})(.{3})', '\1.\2.') new from dual;

NEW
--------------------
Z1237.890.45YH
Re: How to split the serial number [message #337733 is a reply to message #337689] Thu, 31 July 2008 23:57 Go to previous message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Hi Ebrian,
Yes, a different solution...Thanks a lot.

Thanks and Regards,
Hammer. Cool

[Updated on: Thu, 31 July 2008 23:57]

Report message to a moderator

Previous Topic: Number in date column
Next Topic: Create a view based on a public synonym
Goto Forum:
  


Current Time: Wed Dec 07 16:37:26 CST 2016

Total time taken to generate the page: 0.07541 seconds