help with query

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:524963
Hi,
I need to modify the data in a column in CODE.stock_code. Some of the
records are in the form of 250-10-12-9. Not all records are the same lengh,
but I need to remove the -9 in those records.
This newbie would appreciate your help in the sql that would do this.
regards,
g
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04Will the "-9" always appear as the last two characters in the value? If that
is
the case, then you could do the following:
Update Code
Set Stock_Code = Left(Stock_Code, Len(Stock_Code) - 2)
Where Left(Stock_Code, 2) = '-9'
Thomas
"Neither rhyme nor reason" <sportman_6663@.yahoo.ca> wrote in message
news:L38ee.11476$3U.717136@.news20.bellglobal.com...
> Hi,
> I need to modify the data in a column in CODE.stock_code. Some of the
> records are in the form of 250-10-12-9. Not all records are the same lengh
,
> but I need to remove the -9 in those records.
> This newbie would appreciate your help in the sql that would do this.
>
> regards,
> g
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
>|||You said "need to remove the -9 in those records" -- which records (rows)
are "those" ? We need to know more details.. Do you need to remove the '-9'
from any row that Has a '-9' at the end? Id so, then just
Update Table Set
ColName = Left(ColName, Len(ColName) - 2)
Where ColName like '%-9'
-- or is there another rule?
"Neither rhyme nor reason" wrote:

> Hi,
> I need to modify the data in a column in CODE.stock_code. Some of the
> records are in the form of 250-10-12-9. Not all records are the same lengh
,
> but I need to remove the -9 in those records.
> This newbie would appreciate your help in the sql that would do this.
>
> regards,
> g
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
>
>|||You should never have a table named "Code" -- what kind of code is it'
-9 in those records [sic]. <<
You need to learn to use the right words so you will have the right
mindset. Rows are not records and you update columns. Try this.
UPDATE Inventory
SET stock_code
= REVERSE (SUBSTRING (REVERSE(stock_code), 3))
WHERE SUBSTRING (REVERSE(stock_code), 1, 2) = '-9';
There are also proprietary LEFT() and RIGHT() substring functions, but
these are reserved words in Standard SQL. However, they will probably
be faster.|||Joe, shouldn't that be
Update Inventory
Set Stock_Code = Reverse(Substring(Reverse(Stock_Code), 3))
Where Substring(Reverse(Stock_Code), 1, 2) = '9-'
With the string reversed, the 9 will appear before the dash.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115239637.214708.255220@.o13g2000cwo.googlegroups.com...
> You should never have a table named "Code" -- what kind of code is it'
>
> -9 in those records [sic]. <<
> You need to learn to use the right words so you will have the right
> mindset. Rows are not records and you update columns. Try this.
> UPDATE Inventory
> SET stock_code
> = REVERSE (SUBSTRING (REVERSE(stock_code), 3))
> WHERE SUBSTRING (REVERSE(stock_code), 1, 2) = '-9';
> There are also proprietary LEFT() and RIGHT() substring functions, but
> these are reserved words in Standard SQL. However, they will probably
> be faster.
>|||Thank you all for your help... So my wording should have been
I need to remove the last 2 digits from rows that contain -9 in the
STOCK_CODE column ?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115239637.214708.255220@.o13g2000cwo.googlegroups.com...
> You should never have a table named "Code" -- what kind of code is it'
>
> -9 in those records [sic]. <<
> You need to learn to use the right words so you will have the right
> mindset. Rows are not records and you update columns. Try this.
> UPDATE Inventory
> SET stock_code
> = REVERSE (SUBSTRING (REVERSE(stock_code), 3))
> WHERE SUBSTRING (REVERSE(stock_code), 1, 2) = '-9';
> There are also proprietary LEFT() and RIGHT() substring functions, but
> these are reserved words in Standard SQL. However, they will probably
> be faster.
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04|||Then my modified version of Joe's code should do it:
Update Inventory
Set Stock_Code = Reverse(Substring(Reverse(Stock_Code), 3))
Where Substring(Reverse(Stock_Code), 1, 2) = '9-'
Thomas
"Neither rhyme nor reason" <sportman_6663@.yahoo.ca> wrote in message
news:ogpee.13540$3U.936989@.news20.bellglobal.com...
> Thank you all for your help... So my wording should have been
> I need to remove the last 2 digits from rows that contain -9 in the
> STOCK_CODE column ?
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1115239637.214708.255220@.o13g2000cwo.googlegroups.com...
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
>