Updating Data - A query

[Alan Payne]

First of all I don't normally use SQL, so please excuse my ignorance.

1st Query

We have a table with a column with data that looks like this:
AMTL03256636

What I need to do is change the AMTL portion of the column to another set of
charachters i.e TEST
Currently i'm extracting the data, loading it into excel changing the data
and then bulk inserting it back
into the SQL7 table. Is there away to change the AMTL without the other
steps?



Alan

[Muhd]

Re: Updating Data - A query

Well TSQL has two functions that would be helpful, REPLACE and SUBSTRING,
basically you search for a matching substring then, if found, replace with
one of your choosing. I have only ever worked with MS SQL Server 7 and 2000
so i don't know whats standard SQL vs. TSQL. Its shame really but thats a
whole nother topic.

</Muhd>

"Alan Payne" <Hiram P Hogwash@bigburp .com> wrote in message
news:XHlOb.1668 9$Wa.3963@news-server.bigpond. net.au...[color=blue]
> First of all I don't normally use SQL, so please excuse my ignorance.
>
> 1st Query
>
> We have a table with a column with data that looks like this:
> AMTL03256636
>
> What I need to do is change the AMTL portion of the column to another set[/color]
of[color=blue]
> charachters i.e TEST
> Currently i'm extracting the data, loading it into excel changing the data
> and then bulk inserting it back
> into the SQL7 table. Is there away to change the AMTL without the other
> steps?
>
>
>
> Alan
>
>[/color]

[Erland Sommarskog]

Re: Updating Data - A query

Alan Payne (Hiram P Hogwash@bigburp .com) writes:[color=blue]
> First of all I don't normally use SQL, so please excuse my ignorance.
>
> 1st Query
>
> We have a table with a column with data that looks like this:
> AMTL03256636
>
> What I need to do is change the AMTL portion of the column to another
> set of charachters i.e TEST Currently i'm extracting the data, loading
> it into excel changing the data and then bulk inserting it back into the
> SQL7 table. Is there away to change the AMTL without the other steps?[/color]

UPDATE tbl
SET col = replace(col, 'AMTL', 'TEST')
WHERE col LIKE 'AMTL%'

Here I am presuming that AMTL is a fixed string, as you gave no further
specification to your problem.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Alan Payne]

Re: Updating Data - A query


"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns9474AE6 2B8C60Yazorman@ 127.0.0.1...[color=blue]
> Alan Payne (Hiram P Hogwash@bigburp .com) writes:[color=green]
> > First of all I don't normally use SQL, so please excuse my ignorance.
> >
> > 1st Query
> >
> > We have a table with a column with data that looks like this:
> > AMTL03256636
> >
> > What I need to do is change the AMTL portion of the column to another
> > set of charachters i.e TEST Currently i'm extracting the data, loading
> > it into excel changing the data and then bulk inserting it back into the
> > SQL7 table. Is there away to change the AMTL without the other steps?[/color]
>
> UPDATE tbl
> SET col = replace(col, 'AMTL', 'TEST')
> WHERE col LIKE 'AMTL%'
>
> Here I am presuming that AMTL is a fixed string, as you gave no further
> specification to your problem.
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

Thank you for your assistance, that does the trick exactly.


Alan