sql server programming: Quick question.

I have the column with the value NA0042379 (nine characters). I would like
to delete the 'A' from this value so it will become N0042379 (eight
characters). I have tried using the SUBSTRING function, but am only able to
use it in a select statement. I would like to actually UPDATE the value to
N0042379. I have about 100+ values similar to the one above all beginning
with NA%. How do I update all these values to delete the 'A' so that I can
have eight characters instead of nine characters?

DECLARE @str VARCHAR(10)
SET @str='NA0005152'
SELECT REPLACE(@str,'A','')






[quoted text, click to view]

Likely the only reasonable answer is to redesign this value. If you need to
deal with it in parts, you need to store it in parts. Do each of the values
have individual values? Unless someone just happened to be offended by NA
together....

You can use substring in the SET clause and WHERE clauses

update <tablename>
set columnName = 'N' + substring (columnName, 3, 6) --untested,
substring might not be 100% right
where columnName like 'NA%' --didn't need it in the where clause, but it
will work

My guess is that what you really need is:

create table <tablename>
(
firstPart varchar(2), --or 1, depending on what you are actually trying
to do
secondPart varchar(7),
...,
concat as firstPart + secondPart
)

Obviously there may be more parts, and the data may be more complex, but who
knows :)

--
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
[quoted text, click to view]

[quoted text, click to view]

Hi Reese21,

If there will only be one A in these values:

UPDATE MyTable
SET MyColumn = REPLACE (MyColumn, 'A', '')
WHERE MyColumn LIKE 'NA%'

If there might be more than one A and you want to remove only the first:

UPDATE MyTable
SET MyColumn = LEFT(MyColumn, 1) + SUBSTRING(MyColumn, 3, 7)
WHERE MyColumn LIKE 'NA%'

Best, Hugo
--