String functions: Remove non-alpha characters, remove multiple spaces

Search hint:

remove non-alpha characters from a string

replace multiple spaces with single space

remove multiple spaces

convert to title case

convert to name case


DECLARE @inputString nvarchar(1000);
/*remove non-alpha characters from a string*/
SET @inputString=’ (My name is): MR. [Masud AHMED]. \Some unknown /characters- ÄÄ ÖÖ ÜÜ ÉÉ ØØ ?? ÆÆ ‘;
PRINT ‘input string: ‘ + @inputString;

/*list of all the non-alpha characters except a space ( ) and a dot (.) I want to preserve spaces between words*/
SET @NonAlphaList = ‘%[^a-z .]%’;
/*look for the non-alpha character position in a string*/
WHILE PATINDEX(@NonAlphaList, @inputString) > 0
/*delete the non-alpha character and replace the character with nothing*/
SET @inputString = STUFF(@inputString, PATINDEX(@NonAlphaList, @inputString), 1, ”);
PRINT ‘removed non-alpha: ‘ + @inputString;

/*remove multiple spaces*/
/*select string = replace(replace(replace(@inputString,’ ‘,'<>’),’><‘,”),'<>’,’ ‘)*/
SET @inputString= REPLACE(@inputString, ‘ ‘, ‘<>’);
SET @inputString= REPLACE(@inputString, ‘><‘, ”);
SET @inputString= REPLACE(@inputString, ‘<>’, ‘ ‘);
SET @inputString=LTRIM(RTRIM(@inputString));
PRINT ‘removed multiple spaces: ‘ + @inputString;

/*Title Case*/
DECLARE @index tinyint=1;
DECLARE @currentChar CHAR(1);
SET @inputString=LOWER(@inputString);
WHILE @index<LEN(@inputString)
SET @currentChar=SUBSTRING(@inputString, @index, 1);
IF (@index=1)
SET @inputString=STUFF(@inputString, @index, 1, UPPER(@currentChar))
IF(@currentChar=’ ‘)
SET @inputString=STUFF(@inputString, (@index + 1), 1, UPPER(SUBSTRING(@inputString, @index + 1, 1)))
SET @index+=1;

PRINT ‘convert to Title Case: ‘ + @inputString;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s