Use SQL To Change A Substring
February 9, 2016 Ted Holt
|
Occasionally I need to change part of a string value in a database table. SQL has three mechanisms that let me accomplish such a thing. Do you know what those three mechanisms are and how to use them? First, we need a table for illustrative purposes.
create table Parts
( ID char(6) primary key,
Description varchar(40),
Cost dec (5,2),
Price dec (5,2));
insert into parts values
('XX-101', '20-inch Doodad', 0.12, 0.25),
('XX-105', '2-foot Doodad left-hand', 0.48, 0.75),
('XX-106', '2-foot Doodad right-hand', 0.48, 0.75),
('XX-118', '12-foot Doodad', 1.25, 3.00),
('BA-101', '5CM Widget', 0.30, 0.45),
('BA-104', '15Cm Widget', 1.00, 1.35),
('BA-111', '150cm Widget', 5.00, 8.00),
('BA-145', 'Used 15cm Widget', 0.20, 0.80),
('CT-201', 'ACME chain stretcher', 4.75, 19.99),
('CT-202', 'Refurbished ACME sky hook', 3.00, 12.00);
Mechanism 1: Substring and Concatenation You can use the substring function and concatenation to build a new value for a string. This method is appropriate when you know where the substring begins. For example, some of the parts have IDs that begin with “XX”. Let’s change “XX” to “AA”. update parts set ID = 'AA' concat substr(ID,3) where ID between 'XX' and 'XX9' I concatenated the string “AA” to the third and following characters of the ID and assigned the result back to the ID column. Now the table looks like this:
The string that is to be replaced and the replacement string do not have to be the same length. That happened to be the case in this example. Mechanism 2: The REPLACE Function When you don’t know where a substring will begin, you can use the REPLACE function to replace one string with another. Let’s replace the barbaric term “doodad” with the more elegant and urbane term “doohickey”. update parts set Description = replace(Description, 'Doodad', 'Doohickey') where ID between 'AA' and 'AA9' Look at the descriptions now.
The WHERE clause was not necessary, but including it prevented SQL from updating other rows unnecessarily. Mechanism 3: Regular Expressions Regular expressions are a cryptic but powerful type of wild card processing. To learn more about them, read Michael Sansoterra’s superb explanation. Let’s change the parts that are measured in centimeters in two ways. First, let’s make sure that the abbreviation for centimeter, cm, is in lowercase letters. Second, let’s leave only one space between cm and the following text. Here’s the update:
update parts as p
set p.description = regexp_replace(p.description,
'([0-9]+)[Cc][Mm]( +)','$1cm ')
where regexp_like (p.description,'[0-9]+[Cc][Mm]')
Look at the table now:
The WHERE clause uses the regular expression like function to select the rows that have a number immediately followed by the letters “CM” in any case. Look more closely. where regexp_like (p.description,'[0-9]+[Cc][Mm]')
The regular expression replace function changes part of the string. regexp_replace(p.description, '([0-9]+)[Cc][Mm]( +)','$1cm ')
When such a match is found, the system uses the third parameter to replace the matched text.
The remainder of the description is not part of the match, and is therefore not affected. So there you have it. Three replacement mechanisms. If you need to replace part of a string, you can handle it. RELATED STORY Native Regular Expressions In DB2 For i 7.1 And 7.2
|
