Home » SQL & PL/SQL » SQL & PL/SQL » concatenation of a string and an autoincremented sequence (windows xp, oracle 8i)
concatenation of a string and an autoincremented sequence [message #305719] Tue, 11 March 2008 13:23 Go to next message
gargi_12300
Messages: 11
Registered: March 2008
Junior Member
Hello all
please kindly help me to solve this problem:
I have a table in which I need to generate an unique id for new users.
The id goes like this:
D001B001CTZ001
I have stored D001B001 in a vharchar2(20) type variable say a1.
and now I have created an autoincremented sequence for the last 3 digits of the id.I stored it in seq_num
now I am writing the statement as:
u_id=a1||'CTZ'||'seq.num';
It compiles successfully but doesnot gives any value for u_id.
Can anyone help me with the code to generate this id.
How can I concat a string with an integer.
please help me!!!
Re: concatenation of a string and an autoincremented sequence [message #305722 is a reply to message #305719] Tue, 11 March 2008 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you posted the actual code and how you use it maybe we could help you.

By the way 'seq.num' is a string and not a sequence number or whatever you think it is.

Regards
Michel
Re: concatenation of a string and an autoincremented sequence [message #305724 is a reply to message #305719] Tue, 11 March 2008 13:54 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Check out the to_char function and then figure it out.
Re: concatenation of a string and an autoincremented sequence [message #305728 is a reply to message #305724] Tue, 11 March 2008 14:14 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Well, it would really help if you posted your code rather than a quick snippet. However, based on what you did post, it won't work. The "=" is a comparison string. You want ":=" which is used to assign a value to a variable. So, you should have...

  u_id := a1 || 'CTZ' || 'seq.num';


which, as also mentioned should give you the string "D001B001CTZseq.num".

As for your autoincremented sequence for the last three digits, again, no code shown so no idea on how to help.

Ron
Re: concatenation of a string and an autoincremented sequence [message #305960 is a reply to message #305728] Wed, 12 March 2008 07:35 Go to previous messageGo to next message
gargi_12300
Messages: 11
Registered: March 2008
Junior Member
Thanx Bill B
I got my answer
it is working now.
And I stored the sequence number in seq_num variable.
thanx again
Re: concatenation of a string and an autoincremented sequence [message #305970 is a reply to message #305960] Wed, 12 March 2008 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post the solution you found for others.

Regards
Michel
Re: concatenation of a string and an autoincremented sequence [message #305998 is a reply to message #305719] Wed, 12 March 2008 09:30 Go to previous messageGo to next message
gargi_12300
Messages: 11
Registered: March 2008
Junior Member
the code goes like this:
u_id= a1||'CTZ'||to_char(seq_num);
Regards
gargi
Re: concatenation of a string and an autoincremented sequence [message #306003 is a reply to message #305998] Wed, 12 March 2008 09:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take care that to_char does not give you leading 0 by default, you have to provide a format.

Regards
Michel
Re: concatenation of a string and an autoincremented sequence [message #306037 is a reply to message #305719] Wed, 12 March 2008 11:20 Go to previous messageGo to next message
gargi_12300
Messages: 11
Registered: March 2008
Junior Member
yes its true...
can you suggest the solution I am facing that problem..
Re: concatenation of a string and an autoincremented sequence [message #306040 is a reply to message #306037] Wed, 12 March 2008 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
to_char(col,'00000')

Regards
Michel
Re: concatenation of a string and an autoincremented sequence [message #306041 is a reply to message #306037] Wed, 12 March 2008 11:31 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You have been told to look at the TO_CHAR function. This should help http://www.ss64.com/orasyntax/numfmt.html

Re: concatenation of a string and an autoincremented sequence [message #306042 is a reply to message #305719] Wed, 12 March 2008 11:39 Go to previous message
gargi_12300
Messages: 11
Registered: March 2008
Junior Member
thanxx a lot
this surely helps...
Previous Topic: Wait whila a job is running
Next Topic: SQL - Need Help Urgently
Goto Forum:
  


Current Time: Sun Dec 04 06:32:48 CST 2016

Total time taken to generate the page: 0.09411 seconds