• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 44
  • Last Modified:

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.
0
LD16
Asked:
LD16
  • 2
2 Solutions
 
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.
0
 
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.
0
 
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

1
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now