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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Got the column should be build as following:
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.
LEFT([SPoTInternalId], CHARINDEX('_', [SPoTInternalId]) - 1) AS [Revised_SPOT_Internal_ID],
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.
First of all verify with a SELECT you get the correct truncation:
Then do the update:
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.
SELECT SUBSTRING(SPOTinternalID,0, CHARINDEX('_' ,SPOTinternalID )) as LeftPartOfSPOTinternalID
FROM mstt_schema.SPOT_MSTT_MATCH
WHERE CHARINDEX('_' ,SPOTinternalID )>0;
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;
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.
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
BEFORE UPDATE DATA
SELECT * FROM SPOT_MSTT_MATCH
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
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)
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
/*------------------------
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)
/*------------------------
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)
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial