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.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LD16Author Commented:
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.
Olaf DoschkeSoftware DeveloperCommented:
First of all verify with a SELECT you get the correct truncation:
SELECT SUBSTRING(SPOTinternalID,0, CHARINDEX('_' ,SPOTinternalID )) as LeftPartOfSPOTinternalID 
FROM mstt_schema.SPOT_MSTT_MATCH
WHERE CHARINDEX('_' ,SPOTinternalID )>0;

Open in new window


Then do the update:
UPDATE mstt_schema.SPOT_MSTT_MATCH SET MSTTID =  SUBSTRING(MSTTID,0, CHARINDEX('_' ,MSTTID))
WHERE CHARINDEX('_' ,MSTTID)>0;

UPDATE mstt_schema.SPOT_MSTT_MATCH SET  SPOTID =  SUBSTRING( SPOTID,0, CHARINDEX('_' , SPOTID))
WHERE CHARINDEX('_' , SPOTID)>0;

UPDATE mstt_schema.SPOT_MSTT_MATCH SET SPOTinternalID =  SUBSTRING(SPOTinternalID,0, CHARINDEX('_' ,SPOTinternalID))
WHERE CHARINDEX('_' ,SPOTinternalID)>0;

Open in new window


Even though your sample data shows all columns having underscores, if one column has them, you should test and update separately, so you have 3 updates and don't miss a single value, that way.

Bye, Olaf.
Pawan KumarDatabase ExpertCommented:
Please try full tested solution for this.

Note - You do not need 3 update statements for this. Only 1 statement will do it. Using multiple update statement is BAD for performance.

DATA GENERATTON

CREATE TABLE SPOT_MSTT_MATCH
(
	 Cardinality VARCHAR(10) 
	,MSTTID      VARCHAR(50)
	,SPOTID      VARCHAR(50)
	,SPoTInternalID VARCHAR(50)
)
GO

INSERT INTO SPOT_MSTT_MATCH VALUES
('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'),
('1-1','1CKL8TV0171109','1CKL8TV20171109','90123820171109')
GO

Open in new window


BEFORE UPDATE DATA

SELECT * FROM SPOT_MSTT_MATCH
/*------------------------
SELECT * FROM SPOT_MSTT_MATCH
------------------------*/
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
1-1         1CKL8TV0171109                                     1CKL8TV20171109                                    90123820171109

(5 row(s) affected)

Open in new window

UPDATE SOLUTION

UPDATE x
	SET 
	  MSTTID = CASE WHEN CHARINDEX('_' ,MSTTID ) > 0 THEN SUBSTRING(MSTTID,0,CHARINDEX('_' ,MSTTID )) ELSE MSTTID END
    , SPOTID  = CASE WHEN CHARINDEX('_' ,SPOTID ) > 0 THEN SUBSTRING(SPOTID,0,CHARINDEX('_' ,SPOTID )) ELSE SPOTID END
	, SPoTInternalID  = CASE WHEN CHARINDEX('_' ,SPoTInternalID ) > 0 
			THEN SUBSTRING(SPoTInternalID,0,CHARINDEX('_' ,SPoTInternalID )) ELSE SPoTInternalID END 
FROM SPOT_MSTT_MATCH x
WHERE CHARINDEX('_' ,SPOTinternalID ) > 0 OR CHARINDEX('_' ,MSTTID ) > 0 OR CHARINDEX('_' ,SPOTID ) > 0

Open in new window

UPDATE MESSAGE

/*------------------------
UPDATE x
	SET 
	  MSTTID = CASE WHEN CHARINDEX('_' ,MSTTID ) > 0 THEN SUBSTRING(MSTTID,0,CHARINDEX('_' ,MSTTID )) ELSE MSTTID END
    , SPOTID  = CASE WHEN CHARINDEX('_' ,SPOTID ) > 0 THEN SUBSTRING(SPOTID,0,CHARINDEX('_' ,SPOTID )) ELSE SPOTID END
	, SPoTInternalID  = CASE WHEN CHARINDEX('_' ,SPoTInternalID ) > 0 
			THEN SUBSTRING(SPoTInternalID,0,CHARINDEX('_' ,SPoTInternalID )) ELSE SPoTInternalID END 
FROM SPOT_MSTT_MATCH x
WHERE CHARINDEX('_' ,SPOTinternalID ) > 0 OR CHARINDEX('_' ,MSTTID ) > 0 OR CHARINDEX('_' ,SPOTID ) > 0
------------------------*/

(4 row(s) affected)

Open in new window

AFTER UPDATE DATA

/*------------------------
SELECT * FROM SPOT_MSTT_MATCH
------------------------*/
Cardinality MSTTID              SPOTID             SPoTInternalID
----------- ------------------- ------------------ ---------------
1-1         1BN6Q5F             1BN6Q5F            439378
1-1         1C236S2             1C236S2            574109
1-1         1CKL7AK             1CKL7AK            901162
1-1         1CKL8TV             1CKL8TV            901238
1-1         1CKL8TV0171109      1CKL8TV20171109    90123820171109

(5 row(s) affected)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.