CODEWITHSUNDEEP
mysqlsql
Asif Shahzad
Asif Shahzad

Reputation: 853

How to update a value with substring of current value using SQL

In my location table, some city names contain zip codes. I want to remove the zip code. Zip Codes (if any) are present at right side of the city name. (I am using MySQL)

I can select all such cities with this query:

SELECT name FROM location where name REGEXP '[0-9]$';

It displays:

Lahore-54000
Karachi-75000
Islamabad-87544

Now, I want to update the name column with zip codes removed.
How I can compose the update query?

Upvotes: 3

Views: 13325

Answers (1)

Devart
Devart

Reputation: 121912

Part1

Try this one -

UPDATE location
SET
  name = LEFT(name, LENGTH(SUBSTRING_INDEX(name, '-', -1)) + 1)
WHERE
  name REGEXP '[0-9]$';

Part2

Additional function:

DELIMITER $$

CREATE FUNCTION mytrim(input_string VARCHAR(255))
RETURNS VARCHAR(255) CHARSET utf8
BEGIN
  SET @pos = NULL;
  SET @len = length(input_string);
  SET @i = @len;

  WHILE @i > 0
  DO
    SET @c = substring(input_string, @i, 1);
    IF (ascii(@c) > 47 AND ascii(@c) < 58) OR @c = '-' THEN
      SET @pos = @i;
    ELSE
      SET @i = -1;
    END IF;

    SET @i = @i - 1;
  END WHILE;

  RETURN if(@pos IS NULL, input_string, substring(input_string, 1, @pos - 1));
END
$$

DELIMITER ;

Examples:

SET @n1 = "564, garden-block, Karachi-75000";
SET @n2 = "55, abc-block, Karachi 75870";
SELECT mytrim(@n1), mytrim(@n2);
+----------------------------+-------------------------+
| mytrim(@n1)                | mytrim(@n2)             |
+----------------------------+-------------------------+
| 564, garden-block, Karachi | 55, abc-block, Karachi  |
+----------------------------+-------------------------+

Try to use it to replace values:

UPDATE location
SET
  name = mytrim(name)
WHERE
  name REGEXP '[0-9]$';

Upvotes: 5

Related Questions