Avatar of Luis Diaz
Luis Diaz
Flag 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.
Microsoft SQL Server

Avatar of undefined
Last Comment
Olaf Doschke

8/22/2022 - Mon
Luis Diaz

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
Olaf Doschke

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Pawan Kumar

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Olaf Doschke

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck