How to update part of a field?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

How to update part of a field?

Post by voltrader »

I have a large DB where I would like to change fields beginning with "BA" to "Ba".

The records typically have more information, like "BA 1234567". I would like to change this to "Ba 1234567".

How to I compose an SQL query to search through records, find those that start with "BA" and change only that part to "Ba"?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

First, you will need to use BINARY operator to do case-sensitive search, like:

Code: Select all

... where left(binary fieldname, 2) = 'BA'
then you would compose your replacement string using 'Ba' prefix and source string without 2 leading characters:

Code: Select all

... fieldname = concat('Ba', substring(fieldname, 3)) ...
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post by voltrader »

Thanks! Just what I was looking for!
Post Reply