CODEWITHSUNDEEP
mysqlsqlsubstring
EnexoOnoma
EnexoOnoma

Reputation: 8834

How to replace a mySQL column that contains specific text?

I have the table "behavior" and a column "url". The column "url" contains some rows as below.

What I want to do is to change the rows of column "url" such as

/this-is/the-main-url?fbclid=IwAR1VIvXLBSIs4S_eRc4FGVYhIsiViWPEBBSmpkHMatjAcH0Rh-_u4VGoo

to

/this-is/the-main-url

and likewise this

/here-is/another-url?utm_source=facebook&utm_medium=cpc&utm_campaign=fb.traffic

to

/here-is/another-url

of the behavior table.

So, if the string contains ?fbclid or ?utm_source to keep everything before it.

This can be done also by using multiple queries, instead of one complex.

NOTE: The utm_source or fbclid, parameters and ids can be various, so I need a dynamic solution.

Thank you

Upvotes: 1

Views: 342

Answers (2)

spencer7593
spencer7593

Reputation: 108500

MySQL SUBSTRING_INDEX function is convenient.

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index

Demonstration

SELECT SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           t.foo 
           ,'?fbclid'
           ,1
         )
         ,'?utm_source'
         ,1
       )
  FROM ( SELECT '/this-is/the-main-url?fbclid=IwAR1VIvXLBSIs4S_eRc4FGVYhIsiViWPEBBSmpkHMatjAcH0Rh-_u4VGoo' AS foo 
         UNION ALL SELECT '/here-is/another-url?utm_source=facebook&utm_medium=cpc&utm_campaign=fb.traffic'
       ) t

Once we have expression working and tested, we can make use of those expressions in an UPDATE statement. But we want to test the expressions in a SELECT statement first.

 UPDATE mytable 
    SET mycol = SUBSTRING_INDEX( mycol ,'?fbclid',1)

FOLLOWUP

The specification in the original question was to remove strings '?fbclid' and ?utm_source from string.

The question is now updated to say "parameters and ids can be various, so I need a dynamic solution."

If we just want to chop everything after (and including) the first question mark character, then we can do this:

  SUBSTRING_INDEX( myurl ,'?',1)

If we want to leave some question mark characters, and only trim off certain strings, then we can invoke SUBSTRING_INDEX multiple times.

As far as making this more dynamic, we can make a call to SUBSTRING_INDEX for each delimiter, and pass in the delimiter argument (the second argument) to SUBSTRING_INDEX as a parameter.

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

this will work indeed:

select substring(colname,1,instr(colname,'?')-1) from tablename;

to change column as a whole :

update tablename set colname=
select substring(colname,1,instr(colname,'?')-1) from tablename;

Upvotes: 0

Related Questions