Need to append a character onto end of data in a column

Question

Need to append a character onto end of data in a column

Express12

SSCoach

Points: 18514
More actions
April 21, 2005 at 10:18 am

#166590

Need T-SQL to append the character "@" onto the end of all Column values (column: SupplyName nvarchar(32))
Example: I'd like the following SOURCE sample values converted to the TARGET values:
SOURCE   TARGET
Abc      Abc@
Xyzzz    Xyzzz@
123abc   123abc@
Bob Cat Bob Cat@

Thx in advance!
BT

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 1

For a one time run:

UPDATE TableName
SET Target = Source + '@'

For multiple runs:

UPDATE TableName
SET Target = Source + '@'
WHERE RIGHT(Target,1)  '@'

That way you only are touching the new records.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

You can also take in consideration that this operation can be done at the select level (if you're not sure you want to alter the underlying data).

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Since the column is rather short (NVARCHAR(32)), I would suggest adding a length check to avoid problems when trying to add another character to entry that already is at maximum length.

UPDATE TableName

SET SupplyName = SupplyName + '@'

WHERE RIGHT(SupplyName,1) <> '@'

AND LEN(SupplyName) < 32

HTH, Vladan

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 4

Good point. I just took the obvious.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Also bear in mind that LEN ignores trailing spaces and if the column was padded with spaces then the concatenation may not work. This is probably not the case here but you never know

Far away is close at hand in the images of elsewhere.
Anon.

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 6

Okay - to cover every realm of possibility :

UPDATE TableName
SET SupplyName = LTRIM(RTRIM(SupplyName)) + '@'
WHERE RIGHT(SupplyName,1)  '@'
AND LEN(SupplyName) < 32

Now, is there anything else we forgot about this simple update query?

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.