add extra char to field help! 1

[croakyfrog]

hi

i was wondering if there is a command that will update a field so that an extra char is added last-but-one to the end, so eg, aaaaa would become aaaaba? (basically take off a and add ba)?

any pointers at all would be good - does anyone have a good site for stuff like this?

 

[MeanGreen]

I don't know of a built in command to do this, but you could do the following:

select substring(<column>,1,datalength(<column>)-1) + 'b' + right(<column>,1) from <table>

 

[AWithers]

try something like this:

update yourtable
set yourfield = left(yourfield,len(yourfield)-1)&&quot;b&quot;&right(yourfield,1)

replace &quot;b&quot; with the string you want to insert.

to ensure its going to give you want to want try this first

select left(yourfield,len(yourfield)-1)&&quot;b&quot;&right(yourfield,1) as newvalue from yourtable

Andy

 

[croakyfrog]

select substring(<column>,1,datalength(<column>)-1) + 'b' + right(<column>,1) from <table>

this gives the following result: eg aaaaa ba (not dropping the last character but adding it with a b+last char after a few spaces

the other example throuws up a message incorrect syntax near word left

any thoughts? could you exaplin the syntax to me - dont worry if its a bother..

)

 

[AWithers]

try this

select left(yourfield,len(yourfield)-1)+'b'+right(yourfield,1) as newvalue from yourtable

and this for the update

update yourtable
set yourfield = left(yourfield,len(yourfield)-1)+'b'+right(yourfield,1)

Andy

 

[croakyfrog]

it still doesnt like the left bit - it's sql 6 if that makes much difference

 

[osjohnm]

Hi croakyfrog

Try the following syntax:

update table
set column = replace(column, left(right(column, 2),1), 'B')
from table where etc...


that should do the trick

John

 

[MeanGreen]

Try this:

select substring(<column>,1,datalength(rtrim(<column>))-1) + 'b' + substring(<column>,datalength(rtrim(<column>)),1) from <table>

For the pubs database:

select substring(au_lname,1,datalength(rtrim(au_lname))-1) + 'b' +
substring(au_lname,datalength(rtrim(au_lname)),1),au_lname from authors

and it works.

Good Luck.

 

[SheetsERR]

Hey, folks!

I've been curious about this syntax for a while, but I never really had a reason to use it. This thread gave me the gusto to give it a try.

The syntax that Andy doled out above works just fine in SQL Server 2000 - not sure about 6x.

SheetsERR

 

[croakyfrog]

that works!! brilliant thank you very much --<--@ (that is a rose by the way, to say thanks!)

i am a very happy girly today now