Computed Column Specification formula

Question

Computed Column Specification formula

w.bartelink

Valued Member

Points: 54
More actions
October 31, 2018 at 8:42 am

#367615

Hi,
hopefully I'm @ the right place to ask; how and can I use the Computed Column Specification formula to replace a letter in data
let me explain; In column 1 data is P1234-S01, Column 2 should now be P1234-R01 (change S to R)
if possible, what is the formula to use at Computed Column Specification
many thanks in advance
best,
Willem

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

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

ZZartin SSC-Dedicated Points: 30899 More actions · Answer 1

Yes, with the qualifier that how complicated the formula would be depends on how standardized the data in the original column is.

sgmunson SSC Guru Points: 110639 More actions · Answer 2

w.bartelink - Wednesday, October 31, 2018 8:42 AM
Hi,
hopefully I'm @ the right place to ask; how and can I use the Computed Column Specification formula to replace a letter in data
let me explain; In column 1 data is P1234-S01, Column 2 should now be P1234-R01 (change S to R)
if possible, what is the formula to use at Computed Column Specification
many thanks in advance
best,
Willem

We don't have enough information here. Does column 1 always have the exact same length? Does it always have the exact same format? Are there any rows in the table for which no change or a different change would be made? You have to be absolutely certain that you cover ALL POSSIBLE conditions for the data in column 1. Once we have those details, then we can construct an expression that will follow them.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

w.bartelink Valued Member Points: 54 More actions · Answer 3

sgmunson - Wednesday, October 31, 2018 8:52 AM
w.bartelink - Wednesday, October 31, 2018 8:42 AM
Hi,
hopefully I'm @ the right place to ask; how and can I use the Computed Column Specification formula to replace a letter in data
let me explain; In column 1 data is P1234-S01, Column 2 should now be P1234-R01 (change S to R)
if possible, what is the formula to use at Computed Column Specification
many thanks in advance
best,
Willem
We don't have enough information here. Does column 1 always have the exact same length? Does it always have the exact same format? Are there any rows in the table for which no change or a different change would be made? You have to be absolutely certain that you cover ALL POSSIBLE conditions for the data in column 1. Once we have those details, then we can construct an expression that will follow them.

Hi Steve.
thanks for your reply,
The data always looks like this, same length and same format. no other changes should be made
best,
Willem

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Willem

Still a little too vague, really. If you're always replacing the same character with the same other character (S with R), use REPLACE. If you're always changing the character in a particular position to a particular character (6th character to R), use STUFF.

John

w.bartelink Valued Member Points: 54 More actions · Answer 5

John Mitchell-245523 - Wednesday, October 31, 2018 9:22 AM
Willem
Still a little too vague, really. If you're always replacing the same character with the same other character (S with R), use REPLACE. If you're always changing the character in a particular position to a particular character (6th character to R), use STUFF.
John

Hi John,
I was trying to get it working wit replace but doesn't get the formula working
is it something like Replace(column1,"S",6,"R")?
best,
Willem

nigel. SSChampion Points: 11636 More actions · Answer 6

w.bartelink - Wednesday, October 31, 2018 9:27 AM
John Mitchell-245523 - Wednesday, October 31, 2018 9:22 AM
Willem
Still a little too vague, really. If you're always replacing the same character with the same other character (S with R), use REPLACE. If you're always changing the character in a particular position to a particular character (6th character to R), use STUFF.
John
Hi John,
I was trying to get it working wit replace but doesn't get the formula working
is it something like Replace(column1,"S",6,"R")?
best,
Willem

Take a look at the docs for REPLACE: https://docs.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql?view=sql-server-2017
and STUFF: https://docs.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-2017

Nigel
How to post questions

sgmunson SSC Guru Points: 110639 More actions · Answer 7

w.bartelink - Wednesday, October 31, 2018 9:27 AM
John Mitchell-245523 - Wednesday, October 31, 2018 9:22 AM
Willem
Still a little too vague, really. If you're always replacing the same character with the same other character (S with R), use REPLACE. If you're always changing the character in a particular position to a particular character (6th character to R), use STUFF.
John
Hi John,
I was trying to get it working wit replace but doesn't get the formula working
is it something like Replace(column1,"S",6,"R")?
best,
Willem

REPLACE(colum1, 'S', 'R')

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

w.bartelink Valued Member Points: 54 More actions · Answer 8

sgmunson - Wednesday, October 31, 2018 10:15 AM
w.bartelink - Wednesday, October 31, 2018 9:27 AM
John Mitchell-245523 - Wednesday, October 31, 2018 9:22 AM
Willem
Still a little too vague, really. If you're always replacing the same character with the same other character (S with R), use REPLACE. If you're always changing the character in a particular position to a particular character (6th character to R), use STUFF.
John
Hi John,
I was trying to get it working wit replace but doesn't get the formula working
is it something like Replace(column1,"S",6,"R")?
best,
Willem
REPLACE(colum1, 'S', 'R')

Hi John, (and others)

REPLACE(colum1, 'S', 'R') did work great!!
thanks for the help
best,
Willem

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 9

w.bartelink - Wednesday, October 31, 2018 12:28 PM
sgmunson - Wednesday, October 31, 2018 10:15 AM
w.bartelink - Wednesday, October 31, 2018 9:27 AM
John Mitchell-245523 - Wednesday, October 31, 2018 9:22 AM
Willem
Still a little too vague, really. If you're always replacing the same character with the same other character (S with R), use REPLACE. If you're always changing the character in a particular position to a particular character (6th character to R), use STUFF.
John
Hi John,
I was trying to get it working wit replace but doesn't get the formula working
is it something like Replace(column1,"S",6,"R")?
best,
Willem
REPLACE(colum1, 'S', 'R')
Hi John, (and others)
REPLACE(colum1, 'S', 'R') did work great!!
thanks for the help
best,
Willem

The trouble is that if you ever have an "S" that you don't want to convert, it will still be converted. I'd suggest that you tighten up the rules a bit for future bombs that may drop.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)