updating the last two spaces of a column

Question

Post reply

updating the last two spaces of a column

thomasrichardson2000

SSC Eights!

Points: 808
More actions
September 3, 2012 at 3:08 am

#280683

Basically what I am trying to achieve is to update the last two character spaces of a column which is defined as varchar(40),
my update statement looks like this
UPDATE Vehicle SET Version = 'fiesta zetec 1.2 BT' WHERE unitnr = '140'
Instead of this statement, I am looking for a way of appending the letters 'BT' to the end of the version column. Is there an easy way of doing this?

Viewing 9 posts - 1 through 8 (of 8 total)

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

BrainDonor SSCoach Points: 19252 More actions · Answer 1

DECLARE @Version Varchar(50)

SET @Version = 'fiesta zetec 1.2';

SET @Version = @Version + ' BT';

SELECT @Version;

Or have I misunderstood the question?

Steve Hall
Linkedin
Blog Site

Ant-Green SSC Guru Points: 113446 More actions · Answer 2

Simply do a string concatination on the existing version value.

update vehicle set version = version + ' BT' where unitnr = '140'

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

UPDATE Vehicle

SET Version = Version + ' BT'

WHERE unitnr = '140'

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

rhythmk SSCertifiable Points: 7271 More actions · Answer 4

thomasrichardson2000 (9/3/2012)
Basically what I am trying to achieve is to update the last two character spaces of a column which is defined as varchar(40),
my update statement looks like this
UPDATE Vehicle SET Version = 'fiesta zetec 1.2 BT' WHERE unitnr = '140'
Instead of this statement, I am looking for a way of appending the letters 'BT' to the end of the version column. Is there an easy way of doing this?

If did not get the answer, please elaborate with example.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

thomasrichardson2000 SSC Eights! Points: 808 More actions · Answer 5

Thanks so far, I will clarify further. The requirement is that i update the version field with the letters 'BT' at the end, So if the column is defined as varchar(40) they want the letters 'BT' to be the last 2 characters in the field, so If varchar(40) has space for 40 characters they want 'BT' to be character 39 and 40. I hope this makes sense.

BrainDonor SSCoach Points: 19252 More actions · Answer 6

DECLARE @Version char(40)

SET @Version = 'fiesta zetec 1.2';

SET @Version = CAST(@Version AS Char(38)) + 'BT';

SELECT @Version;

One method - there will be others.

Steve Hall
Linkedin
Blog Site

rhythmk SSCertifiable Points: 7271 More actions · Answer 7

thomasrichardson2000 (9/3/2012)
Thanks so far, I will clarify further. The requirement is that i update the version field with the letters 'BT' at the end, So if the column is defined as varchar(40) they want the letters 'BT' to be the last 2 characters in the field, so If varchar(40) has space for 40 characters they want 'BT' to be character 39 and 40. I hope this makes sense.

Try this

DECLARE @Version char(40)

SET @Version = 'fiesta zetec 1.2';

SET @Version =SUBSTRING(@Version,1,38) + 'BT';

SELECT @Version

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

thomasrichardson2000 (9/3/2012)
Thanks so far, I will clarify further. The requirement is that i update the version field with the letters 'BT' at the end, So if the column is defined as varchar(40) they want the letters 'BT' to be the last 2 characters in the field, so If varchar(40) has space for 40 characters they want 'BT' to be character 39 and 40. I hope this makes sense.

Maintaining datatype of @Version:

DECLARE @Version Varchar(40)

SET @Version = 'fiesta zetec 1.2';

SET @Version = LEFT(@Version + SPACE(40),38) + 'BT';

SELECT @Version;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden