Fórum SUBSTRING com Left e Right #594374

CREATE FUNCTION dbo.fRetEmail(@email VARCHAR(250))
RETURNS VARCHAR(250)
AS BEGIN
	DECLARE @email2 varchar(250)
	DECLARE @arroba int
	DECLARE @espaco int
	DECLARE @posicao int
	SET @email2 = @email
	SET @arroba = CHARINDEX('@', @email2)
	SET @posicao = @arroba
	SET @espaco = CHARINDEX(' ', @email2, @arroba+1)

	IF @espaco > 0
		SET @email2 = LEFT(@email2, @espaco)

	WHILE @posicao > 1
	BEGIN
		IF SUBSTRING(@email2,@posicao,1)=' '
		BEGIN
			SET @email2 = SUBSTRING(@email2,@posicao+1,LEN(@email2))
			SET @posicao = 1
		END
		SET @posicao = @posicao - 1
	END

	IF @email2 IS NULL
		OR @email2 = ''
		OR @email2 NOT LIKE '[0-9a-zA-Z]%@__%.__%' 
		OR @email2 LIKE '%@%@%' 
		OR @email2 LIKE '%..%' 
		OR @email2 LIKE '%.@' 
		OR @email2 LIKE '%@.' 
		OR @email2 LIKE '%@%.-%' 
		OR @email2 LIKE '%@%-.%' 
		OR @email2 LIKE '%@-%' 
		OR CHARINDEX(' ',LTRIM(RTRIM(@email2))) > 0
		SET @email2 = @email2 + ': não parece um email válido'
 
    RETURN LTRIM(RTRIM(@email2))
END