Changing the 6th character of a string?

[scholzie]

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.

If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)

I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?

TIA,
-CS

[Erland Sommarskog]

scholzie (sc******@gmail.com) writes:

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.

If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)

I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?

Have you looked at substring()?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

CREATE VIEW Parts (.., generic-part, ..)
AS
SELECT .. SUBSTRING part_id,1,5) + '_'[ + SUBSTRING part_id, 7, 1) ,
...
FROM Inventory;

[scholzie]

Thanks. I thought substring() was for finding the instance of a
character, but I guess I read wrong.

Appreciate the help!