Archives of the TeradataForum

Message Posted: Mon, 29 Jul 2002 @ 17:43:33 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Here's a little different approach to changing all occurrences of a character in a string from one value to another. The upside to this approach is that it will change all occurrences in one pass. The downside is that it takes a little more coding.

The basic idea is to update the column by concatenating each character of the column using a series of CASE statements, one per character. (See the example below). The coding of this requires that you know the number of bytes in the column. However, with a little bit of extra effort, it's possible to write SQL using the DBC tables to generate SQL like the one in the example below. Also, by substituting '' (this is two quotes with nothing in between) as the replacement value in the CASE statements, it's possible to eliminate unwanted characters and compress the overall column.

Using the CASE statements on a character by character basis also allows for more flexibility because multiple conditions can be applied to each character. (i.e. if '_', then change to '-', else if blank, change to '*', etc.), there by allowing multiple changes in one pass.

Hope this helps.

Thomas F. Stanek
TFS Consulting
www.tfsconsulting.com

create table test_table
(col_key        integer,
 col_data       char(20)
)
primary index (col_key);

update test_table
   set col_data =

  (case when substr(col_data,01,1) = '-' then '_' else
substr(col_data,01,1)
end)
||(case when substr(col_data,02,1) = '-' then '_' else
substr(col_data,02,1)
end)
||(case when substr(col_data,03,1) = '-' then '_' else
substr(col_data,03,1)
end)
||(case when substr(col_data,04,1) = '-' then '_' else
substr(col_data,04,1)
end)
||(case when substr(col_data,05,1) = '-' then '_' else
substr(col_data,05,1)
end)
||(case when substr(col_data,06,1) = '-' then '_' else
substr(col_data,06,1)
end)
||(case when substr(col_data,07,1) = '-' then '_' else
substr(col_data,07,1)
end)
||(case when substr(col_data,08,1) = '-' then '_' else
substr(col_data,08,1)
end)
||(case when substr(col_data,09,1) = '-' then '_' else
substr(col_data,09,1)
end)
||(case when substr(col_data,10,1) = '-' then '_' else
substr(col_data,10,1)
end)
||(case when substr(col_data,11,1) = '-' then '_' else
substr(col_data,11,1)
end)
||(case when substr(col_data,12,1) = '-' then '_' else
substr(col_data,12,1)
end)
||(case when substr(col_data,13,1) = '-' then '_' else
substr(col_data,13,1)
end)
||(case when substr(col_data,14,1) = '-' then '_' else
substr(col_data,14,1)
end)
||(case when substr(col_data,15,1) = '-' then '_' else
substr(col_data,15,1)
end)
||(case when substr(col_data,16,1) = '-' then '_' else
substr(col_data,16,1)
end)
||(case when substr(col_data,17,1) = '-' then '_' else
substr(col_data,17,1)
end)
||(case when substr(col_data,18,1) = '-' then '_' else
substr(col_data,18,1)
end)
||(case when substr(col_data,19,1) = '-' then '_' else
substr(col_data,19,1)
end)
||(case when substr(col_data,20,1) = '-' then '_' else
substr(col_data,20,1)
end)
 ;

	<Prev	Next>		<<First	<Prev	Next>	Last>>