How To Remove Digits And Special Characters From The Beginning Of A String?

For instance I have '234 - ? Hi there' The result should be: 'Hi there'

Solution 1:

For oracle you have the regexp_replace function. So you could do the below to replace non-alphabetic characters from the beginning of the string:

selectregexp_replace('24 Hi','^([^a-zA-Z]*)','') from dual

The first ^ in ^([^a-zA-Z]*) is to match the beginning of the string. The second ^ is to match any non-alphabetic characters.

Solution 2:

In Oracle you can use REGEXP_REPLACE(). I recommend using a slightly different regex than the one in the accepted answer; there's no reason to do any replacing on a pattern that can be of zero width. Additionally, the parentheses are unnecessary since you don't need to capture a group:

SELECT REGEXP_REPLACE(my_column, '^[^A-Za-z]+') FROM my_table;

We can also exclude the 3rd argument to REGEXP_REPLACE since in Oracle, a NULL and an empty string are equivalent. Another alternative in Oracle is to use the POSIX character class [:alpha:]:

SELECT REGEXP_REPLACE(my_column, '^[^[:alpha:]]+')
  FROM my_table;

Please see the SQL Fiddle here. You can read more about POSIX character classes here.

Solution 3:

Use this Function to remove numeric and Special symbols.

CREATEfunction [dbo].[RemoveNumericandSpecialSymbolValue](@strvarchar(500))  
returnsvarchar(500)  
begindeclare@textintset@text=0  
while 1=1beginset@text= patindex('%[^a-z .]%',@str)  
if @text<>0beginset@str= replace(@str,substring(@str,@text,1),'')  
endelse break;  
endreturn@strend

Example:

select dbo.RemoveNumericandSpecialSymbolValue('234 - ? Hi there')