Page 1 of 1

How to update part of a field?

Posted: Sat Sep 24, 2005 2:46 pm
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"?

Posted: Sat Sep 24, 2005 3:26 pm
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)) ...

Posted: Sat Sep 24, 2005 3:34 pm
by voltrader
Thanks! Just what I was looking for!