append a string to all the values in a column SQL

Sub: Append a string to all the values in a column SQL
Hi all,
This might be a very simple query. But I am new to SQL programming.
Hope someone can help me.
I have a table --
1634 Fred los angeles
123 Sam
1245 abc
1231 ...
1278 ....
578 ...
I want to append TD before the numbers in the first column, I know
there should be a simple statement for this. But I am not getting it as
I have done SQL.
I would like to get
TD1634 Fred los angeles
TD123 Sam
TD1245 abc
TD1231 ...
TD1278 ....
TD578 ...
Hope someone can help me.
Thanks.Hopefully you are doing this in a query, not changing the data in the table,
right? Assuming the column is numeric/integer:
SELECT 'TD' + CONVERT(VARCHAR(12), unnamed_column) FROM unnamed_table;
If the column is already char/varchar then you can say:
SELECT 'TD'+unnamed_column FROM unnamed_table;
<gtg974p@.gmail.com> wrote in message
news:1137170547.476684.150570@.g44g2000cwa.googlegroups.com...
> Sub: Append a string to all the values in a column SQL
> Hi all,
> This might be a very simple query. But I am new to SQL programming.
> Hope someone can help me.
> I have a table --
> 1634 Fred los angeles
> 123 Sam
> 1245 abc
> 1231 ...
> 1278 ....
> 578 ...
> I want to append TD before the numbers in the first column, I know
> there should be a simple statement for this. But I am not getting it as
> I have done SQL.
> I would like to get
> TD1634 Fred los angeles
> TD123 Sam
> TD1245 abc
> TD1231 ...
> TD1278 ....
> TD578 ...
> Hope someone can help me.
> Thanks.
>|||Thanks a lot for your quick reply. As a matter of fact, I would like to
update those values with the new appended values. How should I go about
it.
Thanks again.
Aaron Bertrand [SQL Server MVP] wrote:
> Hopefully you are doing this in a query, not changing the data in the tabl
e,
> right? Assuming the column is numeric/integer:
> SELECT 'TD' + CONVERT(VARCHAR(12), unnamed_column) FROM unnamed_table;
> If the column is already char/varchar then you can say:
> SELECT 'TD'+unnamed_column FROM unnamed_table;
>
>|||We're going to need more information (see http://www.aspfaq.com/5006) such
as what is the actual table name, column names, table structure, etc. Is
that an INT column, does it have the IDENTITY property, etc.
However, I fail to see the value of appending the exact same string to every
single row in a table. Use a view.
CREATE VIEW dbo.Whatever
AS
SELECT unnamed_column = 'TD'+unnamed_column
FROM unnamed_table
GO
Now, when you SELECT unnamed_column FROM dbo.Whatever instead of
unnamed_table, it looks like TD1234, TD15, etc. But you don't have to go
back and update the table every time you add a row.
Another alternative is to use a computed column, like
CREATE TABLE dbo.Foo
(
Column1 VARCHAR(32),
Column2 AS 'TD'+Column1
);
SET NOCOUNT ON;
INSERT dbo.Foo(Column1) SELECT '1234'
INSERT dbo.Foo(Column1) SELECT '15'
SELECT column1, column2 FROM foo;
GO
DROP TABLE dbo.foo;
GO
Again, no updating or perpetual maintenance required.
<gtg974p@.gmail.com> wrote in message
news:1137171379.310459.146110@.z14g2000cwz.googlegroups.com...
> Thanks a lot for your quick reply. As a matter of fact, I would like to
> update those values with the new appended values. How should I go about
> it.
> Thanks again.
>
> Aaron Bertrand [SQL Server MVP] wrote:
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O1Gc1MGGGHA.1676@.TK2MSFTNGP09.phx.gbl...
> We're going to need more information (see http://www.aspfaq.com/5006) such
> as what is the actual table name, column names, table structure, etc. Is
> that an INT column, does it have the IDENTITY property, etc.
> However, I fail to see the value of appending the exact same string to
> every single row in a table. Use a view.
Aaron,
To give this poster the benefit of the doubt, this requirement can arise
when one business assumes another's accounts/customers, and ID numbers
are no longer distinct. I might want to avoid modifying the column, but a
view is not
the answer if there will also need to be prefixes for IDs other than 'TD'.
One option is to put the 'TD' alone in a new column--call it [origin]--and
use
a computed column [origin]+ID for the prefixed IDs. But ultimately that's
not
likely to be a great solution, because a one-column primary key because a
two-column one. Better might be to modify the original column and keep
a separate table matching former IDs with current ones.
Of course, you are right, if the TD is just because management
wants all ID numbers to begin with TD, which is more likely than
my scenario.
Steve Kass
Drew University

> CREATE VIEW dbo.Whatever
> AS
> SELECT unnamed_column = 'TD'+unnamed_column
> FROM unnamed_table
> GO
> Now, when you SELECT unnamed_column FROM dbo.Whatever instead of
> unnamed_table, it looks like TD1234, TD15, etc. But you don't have to go
> back and update the table every time you add a row.
> Another alternative is to use a computed column, like
> CREATE TABLE dbo.Foo
> (
> Column1 VARCHAR(32),
> Column2 AS 'TD'+Column1
> );
> SET NOCOUNT ON;
> INSERT dbo.Foo(Column1) SELECT '1234'
> INSERT dbo.Foo(Column1) SELECT '15'
> SELECT column1, column2 FROM foo;
> GO
> DROP TABLE dbo.foo;
> GO
> Again, no updating or perpetual maintenance required.
>
>
> <gtg974p@.gmail.com> wrote in message
> news:1137171379.310459.146110@.z14g2000cwz.googlegroups.com...
>|||Thanks again.
Let me explain the whole situation.
The table has 5 columns (table name: testchemicals). These are made up
date but in essence this is what it looks like.
No Name Description Value
Date
1. ASDF used in dyeing TD123 Jan
11,2000
2. VBHG used in soda 786 Nov
23, 1983
3. LKJF used in food 1235
Sep 4, 2001
4. POUY used in XYZ TD236 Mar
12, 1999
None of them are linked to anything, so there is no integrity or
referential isues. As you can see the values have two types of naming
scheme. Chemicals tested in one site has TD before them, whereas the
other site didn't use them. Now we want to check, if the data in the
value column starts with TD, if not add TD to it and write it back to
the table. Hope I am making myself clear.
Thanks a lot for helping.|||(gtg974p@.gmail.com) writes:
> The table has 5 columns (table name: testchemicals). These are made up
> date but in essence this is what it looks like.
> No Name Description Value
> Date
> 1. ASDF used in dyeing TD123 Jan
> 11,2000
> 2. VBHG used in soda 786 Nov
> 23, 1983
> 3. LKJF used in food 1235
> Sep 4, 2001
> 4. POUY used in XYZ TD236 Mar
> 12, 1999
> None of them are linked to anything, so there is no integrity or
> referential isues. As you can see the values have two types of naming
> scheme. Chemicals tested in one site has TD before them, whereas the
> other site didn't use them. Now we want to check, if the data in the
> value column starts with TD, if not add TD to it and write it back to
> the table. Hope I am making myself clear.
UPDATE tbl
SET Value = 'TD' + Value
WHERE Value NOT LIKE 'TD%'
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Try this:
Create Table Test
(
Col1 varchar(20)
)
Insert Into Test (Col1)
Values('123')
Insert Into Test (Col1)
Values('158')
Insert Into Test (Col1)
Values('TD243')
Insert Into Test (Col1)
Values('8476')
Insert Into Test (Col1)
Values('1829')
Insert Into Test (Col1)
Values('0938')
Insert Into Test (Col1)
Values('TD8933')
Insert Into Test (Col1)
Values('TD343')
Insert Into Test (Col1)
Values('908')
Update a
Set Col1 = (Case
When Left(a.Col1, 2) = 'TD' Then b.Col1
Else 'TD' + b.Col1
End)
>From Test a
Join Test b on a.Col1 = b.Col1
Select * From Test
Drop Table Test
HTH
Barry|||> To give this poster the benefit of the doubt, this requirement can arise
> when one business assumes another's accounts/customers, and ID numbers
> are no longer distinct. I might want to avoid modifying the column, but a
> view is not
> the answer if there will also need to be prefixes for IDs other than 'TD'.
Well, the poster did say "all the values." :-)|||Based on this last post from you this is clearly a case where the new
requirement should be handled by adding a new attribute to the set - e.g.
adding a new column. I'd suggest strongly against changing the values in the
existing column, since that would simply break first normal form.
ML
http://milambda.blogspot.com/