How to get only alpha or alphanumeric characters from a string in SQL

How to get only alpha chars in SQL

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

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

    Return @Temp
End

Call it like this:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

Capture

How to get alphanumeric characters in SQL

Create Function [dbo].[RemoveNonAlphaNumCharacters](@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

Call it like this:

Select dbo.RemoveNonAlphaNumCharacters('abc1234def5678ghi90jkl')

Capture

Select dbo.RemoveNonAlphaNumCharacters('ab£2+2(")£c123£%"&--4def567&£%"(8ghi90jkl')

Capture

Advertisements

SQL Function to identify invalid email addresses in the database

When you are manipulating large quantities of data, you might have some records containing email addresses which slip through as invalid.

Following function checks these conditions:

No embedded spaces
‘@’ can’t be the first character of an email address
‘.’ can’t be the last character of an email address
There must be a ‘.’ somewhere after ‘@’
the ‘@’ sign is allowed
Domain name should end with at least 2 character extension
can’t have patterns like ‘.@’ and ‘..’

--print dbo.fnc_IsValidEmail('aaa@aaa.com')
--print dbo.fnc_isValidEmail('Mi email@test.com')
CREATE FUNCTION fnc_IsValidEmail
(@Email_Address varchar(255))
RETURNS BIT
BEGIN
DECLARE @Valid bit
SET @Valid = 0
if @Email_Address=''
SET @Valid = 1
ELSE

IF (
CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0
AND LEFT(LTRIM(@email_address),1) <> '@'
AND RIGHT(RTRIM(@email_address),1) <> '.'
AND CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1
AND LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3
AND (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)
)
SET @Valid = 1

RETURN @Valid
END

How to call:

SELECT Email, dbo.fnc_IsValidEmail(Email) as Valid
from tblSuppliers
WHERE Email”

Capture

How to trim leading zeroes in a column/string in SQL

If you have been using this:

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col))

to get your zeroes removed from a string, you might be interested in a variation that only removes the leading zeroes. The T-SQL Function has been created to be called from either Stored Procedure code or Triggers.

--print dbo.ufn_TrimLeadingZeros ('0100 ATN652')
ALTER FUNCTION [dbo].[ufn_TrimLeadingZeros] ( @Input VARCHAR(50) )
RETURNS VARCHAR(50)
AS
BEGIN
    --RETURN REPLACE(LTRIM(REPLACE(@Input, '0', ' ')), ' ', '0')
    RETURN SUBSTRING(@Input, PATINDEX('%[^0]%', @Input+'.'), LEN(@Input))
END