October 31, 2018 at 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
October 31, 2018 at 8:50 am
Yes, with the qualifier that how complicated the formula would be depends on how standardized the data in the original column is.
October 31, 2018 at 8:52 am
w.bartelink - Wednesday, October 31, 2018 8:42 AMHi,
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 Specificationmany 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)
October 31, 2018 at 9:11 am
sgmunson - Wednesday, October 31, 2018 8:52 AMw.bartelink - Wednesday, October 31, 2018 8:42 AMHi,
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 Specificationmany thanks in advance
best,
WillemWe 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
October 31, 2018 at 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
October 31, 2018 at 9:27 am
John Mitchell-245523 - Wednesday, October 31, 2018 9:22 AMWillemStill 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
October 31, 2018 at 9:35 am
w.bartelink - Wednesday, October 31, 2018 9:27 AMJohn Mitchell-245523 - Wednesday, October 31, 2018 9:22 AMWillemStill 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
October 31, 2018 at 10:15 am
w.bartelink - Wednesday, October 31, 2018 9:27 AMJohn Mitchell-245523 - Wednesday, October 31, 2018 9:22 AMWillemStill 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)
October 31, 2018 at 12:28 pm
sgmunson - Wednesday, October 31, 2018 10:15 AMw.bartelink - Wednesday, October 31, 2018 9:27 AMJohn Mitchell-245523 - Wednesday, October 31, 2018 9:22 AMWillemStill 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,
WillemREPLACE(colum1, 'S', 'R')
Hi John, (and others)
REPLACE(colum1, 'S', 'R') did work great!!
thanks for the help
best,
Willem
October 31, 2018 at 12:59 pm
w.bartelink - Wednesday, October 31, 2018 12:28 PMsgmunson - Wednesday, October 31, 2018 10:15 AMw.bartelink - Wednesday, October 31, 2018 9:27 AMJohn Mitchell-245523 - Wednesday, October 31, 2018 9:22 AMWillemStill 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,
WillemREPLACE(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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply