Removing un-wanted text from strings in Oracle
There are different ways of removing unwanted characters from the string using:• REPLACE function
• TRANSLATE function
• REGEXP_REPLACE function (10g and above)
1) Removes special characters from a string value. 2) All characters except 0-9, a-z and A-Z are removed and 3) the remaining characters are returned.
create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
with schemabinding
begin
if @s is null
return null
declare @s2 varchar(256)
set @s2 = ''
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l begin
declare @c int
set @c = ascii(substring(@s, @p, 1))
if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
set @s2 = @s2 + char(@c)
set @p = @p + 1
end
if len(@s2) = 0
return null
return @s2
end
hen call this function in update statement:-UPDATE .
SET =
(
CASE
WHEN LIKE '%[^a-zA-Z0-9]%'
THEN (SELECT dbo.RemoveSpecialChars(ProfessionalName))
ELSE
END
)
select TABLE_NAME , COLUMN_NAME
From dba_tab_columns
WHERE OWNER ='IFSAPP'
AND TABLE_NAME ='ABC_CLASS'
AND DATA_TYPE ='VARCHAR2'
頁:
[1]