append a string to all the values in a column SQL

13 Jan 2006 4:42 PM

gtg974p

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.

13 Jan 2006 4:48 PM

Aaron Bertrand [SQL Server MVP]

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;

<gtg9***@gmail.com> wrote in message
Show quote

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.
>

13 Jan 2006 4:56 PM

gtg974p

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:
Show quote

> 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;
>
>
>
>

13 Jan 2006 5:05 PM

Aaron Bertrand [SQL Server MVP]

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.

<gtg9***@gmail.com> wrote in message
Show quote

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:
>> 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;
>>
>>
>>
>>
>

13 Jan 2006 5:40 PM

Steve Kass

"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

Show quote

>
> 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.
>
>
>
>
> <gtg9***@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:
>>> 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;
>>>
>>>
>>>
>>>
>>
>
>

13 Jan 2006 7:13 PM

Aaron Bertrand [SQL Server MVP]

> 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." :-)

13 Jan 2006 5:47 PM

gtg974p

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.

13 Jan 2006 5:55 PM

Erland Sommarskog

(gtg9***@gmail.com) writes:
Show quote

> 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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

13 Jan 2006 6:00 PM

Barry

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

14 Jan 2006 1:03 PM

ML

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/

14 Jan 2006 5:54 PM

adelfouad1955

gtg9***@gmail.com wrote:
Show quote

> 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.