Good day! I write Sql request. In the ticket_history field. name stores the name of the field, its old and new value: %%FieldName%%FaqID%%Value%%2%%OldValue%%1 . You need to return only the new Value.

Using the TRIM function, I delete the repeating start, but how can I delete %%OldValue%% and everything that follows it? Thank!

  SELECT TRIM( LEADING '%%FieldName%%FaqID%%Value%%' FROM TRIM( TRAILING '%%OldValue%%' FROM `ticket_history`.`name`)), `ticket_history`.`change_time`, `users`.`first_name`, `users`.`last_name` FROM `users` INNER JOIN `ticket_history` ON `users`.`id` = `ticket_history`.`change_by` WHERE `ticket_history`.`history_type_id` = 28
  • look towards the solution - techras.wordpress.com/2011/06/02/regex-replace-for-mysql - bmsdave
  • @VadimGorbachev Please try to publish detailed answers containing a specific example of the minimum solution, supplementing them with a link to the source. Answers –references (as well as comments) do not add knowledge to the Runet. - Nicolas Chabanovsky ♦

1 answer 1

I would not use regexps for this task. Here, a simple search for a substring is sufficient:

 SELECT name, SUBSTR(name, 28, INSTR(name, '%%OldValue%%')-28)Value FROM( SELECT '%%FieldName%%FaqID%%Value%%20000%%OldValue%%10' as name UNION ALL SELECT '%%FieldName%%FaqID%%Value%%-12%%OldValue%%-999' UNION ALL SELECT '%%FieldName%%FaqID%%Value%%ABCDEF%%OldValue%%G' )T

result:

 name Value %%FieldName%%FaqID%%Value%%20000%%OldValue%%10 20000 %%FieldName%%FaqID%%Value%%-12%%OldValue%%-999 -12 %%FieldName%%FaqID%%Value%%ABCDEF%%OldValue%%G ABCDEF

If you do not like the use of numeric constants in the query, you can separately calculate the length of the string '%%FieldName%%FaqID%%Value%%' :

 SELECT name, SUBSTR(name, len_pref+1, INSTR(name, '%%OldValue%%')-(len_pref+1))Value FROM( SELECT '%%FieldName%%FaqID%%Value%%20000%%OldValue%%10' as name UNION ALL SELECT '%%FieldName%%FaqID%%Value%%-12%%OldValue%%-999' UNION ALL SELECT '%%FieldName%%FaqID%%Value%%ABCDEF%%OldValue%%G' )T1,( SELECT LENGTH('%%FieldName%%FaqID%%Value%%') as len_pref )T2