Apr 12, 2017

SQL Server Get The Alpha Numeric Text Only From A Column

Lets say there is column with special characters and all. Will say you just want to filter and take the Alpha Numeric Text Only. This scenario comes normally when you want to find duplicate records, just like my case.

In this case you can simply right a SQL Function to filter the text.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE Function [dbo].[GetAlphanumericTextOnly](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Then call the function


1
SELECT [dbo].[GetAlphanumericTextOnly]('Nifal.Nizar1990@Hotmail.com')

You will get the result as NifalNizar1990Hotmailcom

No comments:

JWT Token Decode Using Jquery

When it come to authentication we use many mechanism. Ones the user authenticated we must keep these details somewhere safe. So we can share...