Link to home
Create AccountLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

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_MATCH
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.
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Got the column should be build as following:

LEFT([SPoTInternalId], CHARINDEX('_', [SPoTInternalId]) - 1) AS [Revised_SPOT_Internal_ID],

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.
SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.