asked on SQL: update in order to remove characters after "_"
Hello experts,
I have the following query which allows me to identify the various lines with under_score:
SELECT Cardinality,
MSTTID,
SPOTID,
SPoTInternalID FROM mstt_schema.SPOT_MSTT_MATC
WHERE CHARINDEX('_' ,SPOTinternalID ) > 0
result:
Cardinality MSTTID SPOTID SPoTInternalID
1-1 1BN6Q5F_20171112 1BN6Q5F_20171112 439378_20171112
1-1 1C236S2_20171112 1C236S2_20171112 574109_20171112
1-1 1CKL7AK_20171109 1CKL7AK_20171109 901162_20171109
1-1 1CKL8TV_20171109 1CKL8TV_20171109 901238_20171109
I am looking for an update query in order to remove all the characters which are after _
The expected result is the following:
Cardinality MSTTID SPOTID SPoTInternalID
1-1 1BN6Q5F 1BN6Q5F 439378
1-1 1C236S2 1C236S2 574109
1-1 1CKL7AK 1CKL7AK 901162
1-1 1CKL8TV 1CKL8TV 901238
Thank you very much for your help.
I have the following query which allows me to identify the various lines with under_score:
SELECT Cardinality,
MSTTID,
SPOTID,
SPoTInternalID FROM mstt_schema.SPOT_MSTT_MATC
WHERE CHARINDEX('_' ,SPOTinternalID ) > 0
result:
Cardinality MSTTID SPOTID SPoTInternalID
1-1 1BN6Q5F_20171112 1BN6Q5F_20171112 439378_20171112
1-1 1C236S2_20171112 1C236S2_20171112 574109_20171112
1-1 1CKL7AK_20171109 1CKL7AK_20171109 901162_20171109
1-1 1CKL8TV_20171109 1CKL8TV_20171109 901238_20171109
I am looking for an update query in order to remove all the characters which are after _
The expected result is the following:
Cardinality MSTTID SPOTID SPoTInternalID
1-1 1BN6Q5F 1BN6Q5F 439378
1-1 1C236S2 1C236S2 574109
1-1 1CKL7AK 1CKL7AK 901162
1-1 1CKL8TV 1CKL8TV 901238
Thank you very much for your help.
Microsoft SQL Server
Last Comment
Olaf Doschke
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Using three CASEs will work, yes. For transaction logging, you'll want to modify the data pages once only, and not three times, that's for sure.
How much slower the three updates compared to one are will depend on how sparse or often underscores are.
The main thing I'd not do is do all three updates only on the basis of one field containing an underscore, even if you feel sure.
Bye, Olaf.
How much slower the three updates compared to one are will depend on how sparse or often underscores are.
The main thing I'd not do is do all three updates only on the basis of one field containing an underscore, even if you feel sure.
Bye, Olaf.
Open in new window
The question is in order to update the 3 columns.
Is not a way to perform and update with a @declare variable?
Thank you again for your help.