Removing un-wanted text from strings in Oracle

admin

  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.

1. create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
   
2.        with schemabinding
   
3.     begin
   
4.        if @s is null
   
5.           return null
   
6.        declare @s2 varchar(256)
   
7.        set @s2 = ''
   
8.        declare @l int
   
9.        set @l = len(@s)
   
10.        declare @p int
    
11.        set @p = 1
    
12.        while @p <= @l begin
    
13.           declare @c int
    
14.           set @c = ascii(substring(@s, @p, 1))
    
15.           if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
    
16.              set @s2 = @s2 + char(@c)
    
17.           set @p = @p + 1
    
18.           end
    
19.        if len(@s2) = 0
    
20.           return null
    
21.        return @s2
    
22.        end

複製代碼

hen call this function in update statement:-

1. UPDATE [BuildingPros].[utbProfessionals]
   
2. SET [ProfessionalName] =
   
3.                    (
   
4.                      CASE
   
5.                       WHEN [ProfessionalName] LIKE '%[^a-zA-Z0-9]%'
   
6.                            THEN (SELECT dbo.RemoveSpecialChars(ProfessionalName))
   
7.                       ELSE [ProfessionalName]
   
8.                       END
   
9.                     )

複製代碼

select TABLE_NAME , COLUMN_NAME
From dba_tab_columns
WHERE OWNER ='IFSAPP'
AND TABLE_NAME ='ABC_CLASS'
AND DATA_TYPE ='VARCHAR2'