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

Question

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?

First is good suggestion, thanks. Secondly: Its not issue of DB design, in fact I got some text files data which I need to put into rational structure. Example in question is just for elaboration of problem, actual case differs much. But it is unnecessary to talk those details and it would make the questions difficult to understand. — Asif Shahzad
– Asif Shahzad, Commented Apr 13, 2011 at 19:17

Devart · Accepted Answer · 2011-04-13 13:00:06Z

5

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]$';

edited Apr 13, 2011 at 13:00

answered Apr 13, 2011 at 11:27

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Asif Shahzad Over a year ago

after chaning -1 to 1, it works. it assume there is only one "-". but in my case there are multiple "-" in city name (in fact its a full address). there are also some zip codes which are not prefixed with "-". so instead of using "-", I need some mechanism to filter by '[0-9]$'. more example data ... "564, garden-block, Karachi-75000" "55, abc-block, Karachi 75870"

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related