| Author |
Topic |
|
sureshprabhu
Starting Member
32 Posts |
 Posted - 2007-09-27 : 15:49:54
|
| Hi!I have one table with thousands of records. Now, I have to add one more column to that table. And this new column will contain concatination of two columns in that same table. Is there any command to do this? My table has two columns as 'file', 'extn', now i want to add 'fullname' column to that table, and it will contain the concatinated value of 'file' and 'extn'. If the 'file' field has one record as "myfile", 'extn' has one record as ".doc", I want to insert "myfile.doc" in the new column 'fullname'. Like this my table has thousands of records. How can i do this? Please help me! Thanks in advance! |
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2007-09-27 : 16:05:27
|
| Why would you do this???Just select the data concatenated together in a viewselect file, extn, rtrim(file)+ltrim(extn) as full_name from table"God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-09-27 : 16:09:07
|
| Why create this as a new column? These values should be concatenated in code, or perhaps using a calculated column. Are you going to apply a trigger to the table to handle data changes?e4 d5 xd5 Nf6 |
|
|
|
sureshprabhu
Starting Member
32 Posts |
Posted - 2007-09-27 : 16:22:32
|
| ok. I will explain my problem clearly, so that you can help me easily. For example, I am inserting one file as 'file.doc', upto now its splitting this like 'file' and '.doc' and inserting two different columns. And one more thing is it is checking whether the 'file' is already exists. If exists it is changing its value like 'file-1' and inserting 'file-1' in one column, '.doc' in another column. But, now the problem is if i tried to insert another file like 'file' '.xls', at this time also its changing the name as 'file-1'. I dont want do this. I want to change the name when name and extn exists it should change its name. This is my problem, Please help me! Thanks jhocutt and blindman for your replies.. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-27 : 16:35:17
|
Make it check to see if the combination exists and not just the first column. You can use what Jhocutt provided.
Future guru in the making. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-09-27 : 16:54:51
|
| Sure. How is it "checking" and modifying the file value? Is it using a sproc, or a trigger?e4 d5 xd5 Nf6 |
|
|
|
sureshprabhu
Starting Member
32 Posts |
Posted - 2007-09-27 : 16:56:31
|
| It is using Stored Procedure. |
|
|
|
sureshprabhu
Starting Member
32 Posts |
Posted - 2007-09-27 : 16:57:55
|
| ALTER PROCEDURE [dbo].[SP_InserintoDesigner] @id uniqueidentifier,@fileUrl nvarchar(255),@fileData varbinary(max),@originalName nvarchar(50),@contentType nvarchar(50),@client nvarchar(50),@username nvarchar(50)asDeclare @items intselect @items=count(*)from Files_Designer where OriginalName Like @originalName + '-[0-9]%' Or OriginalName=@originalNameif @items > 0set @originalName= @originalName + '-' + convert(varchar, @items)INSERT INTO Files_Designer( Id, FileUrl, FileData, OriginalName, ContentType, Client, username)VALUES( @id, @FileUrl, @FileData, @originalName, @contentType, @Client, @username) .....this is my storedprocedure using to do checking. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-09-27 : 17:44:19
|
| I don't see where you are inserting the filename and extn separately...e4 d5 xd5 Nf6 |
|
|
|
sureshprabhu
Starting Member
32 Posts |
Posted - 2007-09-29 : 10:56:32
|
| OriginalName is filename and ContentType is extn |
|
|
|
|
Adding two values from two columns