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 [BuildingPros].[utbProfessionals]
- SET [ProfessionalName] =
- (
- CASE
- WHEN [ProfessionalName] LIKE '%[^a-zA-Z0-9]%'
- THEN (SELECT dbo.RemoveSpecialChars(ProfessionalName))
- ELSE [ProfessionalName]
- END
- )
複製代碼
select TABLE_NAME , COLUMN_NAME
From dba_tab_columns
WHERE OWNER ='IFSAPP'
AND TABLE_NAME ='ABC_CLASS'
AND DATA_TYPE ='VARCHAR2'
|