Trim all non-alpha character from SQL string

Posted on 2 mins read

I’have a table with some dirty data, if you select it seems that everything is ok, but since it was imported from an external source, it happens that some string field actually ends with strange non alpha char.

The symptom is that I have some strange behavior on some data, then I verify what is the content of that row, so I issue a Select * from xxx where Name = ‘Azioni’ and got no result, so I select everything that contains Azioni and I found the record. This is the clear symptom that the field contains some strange stuff in it, so I simply to a Copy and paste in the editor to see exactlywhat is stored in the field, and I found

image

Do you see the strange char at the end? So I need a trim function that removes every non alphabetic character from the beginning and the end of the string. Looking in the internet I found this interesting article that lists a function that remove all non alpha char from the string, so I modified to make it remove non-alpha char only from the beginning and the end.

Here it is if you need it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

create Function [dbo].[TrimNonAlphaCharacters](@Temp nVarChar(max))
Returns nVarChar(max)
AS
Begin
 
While PatIndex('[^a-z]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('[^a-z]%', @Temp), 1, '')
 
While PatIndex('%[^a-z]', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^a-z]', @Temp), 1, '')
Return @TEmp
End

I’ve not tested it extensively, just ran it against my data and verified that it solves my problem, so I’m pretty confident that it works, but feel free to post any correction or any better implementation if you have one :)

Gian Maria.