Changing the 6th character?

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,
-CSscholzie wrote:
> 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
Declare @.s VARCHAR(10)
Set @.s = '1234567890'
SELECT STUFF(@.s, 6, 1, '_') -- 12345_7890
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use STUFF...
SELECT STUFF('11265AA', 6, 1, '_')
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"scholzie" <scholzie@.gmail.com> wrote in message
news:1129066851.089291.37230@.o13g2000cwo.googlegroups.com...
> 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
>|||Wow, this is great! Someone recommended using substring and some ugly
concatenations but this works much nicer.
Thanks!|||Why stuff and why not the replace function?|||Got it... REPLACE doesnt take a index arugument. Sorry.