Validar dirección de email con t-sql

Una función bastante recurrente es tener que validar si una dirección de correo electrónico es válida o no.

He hecho una función de de t-sql que valida si un mail es válido o no. En caso de ser válido devuelve el correo electrónico en minúsculas y sin espacios al principio y al final; si no es válido devuelve una cadena vacía.

Esta función no contempla todos los casos pero se aproxima bastante al estándar, por ejemplo la dirección "dircon@y?"@dominio.com sería válido, pero esta función devolverá que no es correcto.

Código de la función:

CREATE FUNCTION [dbo].[fn_CompruebaEmail](@email VARCHAR(255))   

RETURNS VARCHAR(255)  
as  
BEGIN  
     Declare @valid bit  
     Declare @domain as nvarchar(256)
     Declare @str1 Varchar(128)
Declare @i int
 
     IF @email IS NOT NULL   
 SET @email = REPLACE(@email,CHAR(10),'')
 SET @email = REPLACE(@email,CHAR(13),'')
 SET @email = REPLACE(@email,';','')
          SET @email = LOWER(@email)
          Set @email = LTRIM(RTRIM(@email))
          While (CHARINDEX('  ',@email,0) <> 0)
Begin
Set @email = REPLACE(@email,'  ','')
Continue
 End  
 Set @email = LTRIM(RTRIM(@email))
          SET @valid = 0  
          --IF @email like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%'  
          If  Patindex('[a-z,0-9,_,-]%@[a-z,0-9,-]%.[a-z][a-z]%', @email)=1  
AND @email Not Like '%@%\_%'  ESCAPE '\'
             AND @email NOT like '%@%@%'  
             AND CHARINDEX('.@',@email) = 0  
             AND CHARINDEX('..',@email) = 0  
             AND CHARINDEX(',',@email) = 0  
             AND RIGHT(@email,1) between 'a' AND 'z'  
             And Patindex ('%[ &'',":;!+=\/()<>]%', @email) = 0
               SET @valid=1  
     If @email like '%._'
Set @valid = 0
     If @valid = 0
     Begin
Set @email = ''
     End
     
     --Se comprueba el dominio.
     
     If @email <> ''
     Begin
Set @str1 = ''
Set @i = 48
While @i <= 57
Begin
Set @str1 = @str1 + '|' + Char(@i)
Set @i = @i + 1
End

Set @i = 97
While @i <= 122
Begin
Set @str1 = @str1 + '|' + Char(@i)
Set @i = @i + 1
End    
   
Set @str1 = @str1 + '|.'
Set @str1 = @str1 + '|-'
    
Set @domain = RIGHT(@email,LEN(@email)-CHARINDEX('@',@email))
 
If @domain Like '%[^' + @str1 + ']%' escape '|'
Begin
Set @email = '' 
End
 
End
     
     Return @email
END

Búsqueda de caracteres no alfanuméricos en Sql

El otro día tuve que hacer una función en T-Sql que me permitiera comprobar si una dirección de correo electrónico era válida o no. Dentro de esta comprobación tuve que llegar a comprobar que el dominio no tuviera ningún carácter que no fuese letra, número, punto o guión. Después de mucho pensar como hacerlo y preguntarle a google y a https://duckduckgo.com/, encontré la manera de hacerlo con Like y el carácter comodín ^.

El código para detectar si una cadena tiene algún carácter diferente de los válidos, es decir, números, letras, punto y guión, es el siguiente:

Declare @texto as nvarchar(256)
Declare @str Varchar(128)
Declare @i int
Declare @valido int

Set @valido = 1 Set @str1 = '' 
Set @i = 48 

--Añade al varchar todos los números (ascii del 48 al 57) 
--   separados por un | While @i <= 57 
  Begin 
   Set @str = @str + '|' + Char(@i) 
   Set @i = @i + 1 
  End 

Set @i = 97 

--Añade al varchar todas las letras en minúsculas 
-- (ascii del 97 al 122) separados por un |
While @i <= 122 
 Begin 
Set @str = @str + '|' + Char(@i) 
  Set @i = @i + 1 
End 



--Añade al varchar el punto y el guión

Set @str = @str + '|.'
Set @str = @str + '|-' 

--Si el texto tiene algún caracter diferente de los que 
--  están en el varchar, usando como carácter de 
--   escape el | se marca como "NO válido"
-- Para indicar que busque cualquier carácter que no
--   está en el varchar se usa ^ -> no esté 
--   y [] -> Rango de caracteres
If @texto Like '%[^' + @str + ']%' escape '|' 
  Begin 
    Set @valido = '0' 
  End

Extraer datos a csv desde Sql Server

Una manera bastante efectiva y muy rápida para enviar una gran cantidad de datos entre distintos sistemas de base de datos es utilizar ficheros csv.

La definición que da la Wikipedia de los ficheros csv es: 

Los ficheros CSV (del inglés comma-separated values) son un tipo de documento en formato abierto sencillo para representar datos en forma de tabla, en las que las columnas se separan por comas (o punto y coma en donde la coma es el separador decimal: España, Francia, Italia...) y las filas por saltos de línea. Los campos que contengan una coma, un salto de línea o una comilla doble deben ser encerrados entre comillas dobles.

Es decir, es un formato de fichero de texto plano en el que cada línea del fichero representa un registro de nuestra tabla o resultado de la consulta a exportar. Dentro de esa fila, cada campo está separada por una coma o punto y coma. Siendo en España más usado el ';'.

Tanto la exportación como la importación de datos en este tipo de fichero es extraordinariamente rápida. Además en caso de tener que transferirse por red, al ser ficheros de texto, permiten una gran compresión.

La exportación a estos ficheros se puede hacer directamente desde T-Sql, por lo que es posible, por ejemplo, hacer programaciones de exportaciones con un job de Sql Server.

La sentencia necesaria para hacer esta exportación sería la siguiente:

Declare @vchSql as nvarchar(4000)Declare @bcp as nvarchar(4000)

Set @vchSql = 'Select * From TABLA' 
Set @bcp = 'bcp "'+@vchSql+'" queryout D:\Exp\exp.csv -T -t; -c -C' 

exec master..xp_cmdshell @bcp 

Donde:

• En la variable @vchSql se asigna la consulta a exportar
• D:\Exp\Exp.csv es el fichero resultante. La ruta especificada debe ser local o accesible desde el servidor de Sql Server, ya que el proceso se ejecuta en el servidor.
• En el parámetro -t se asigna el carácter de separación de campos, en este caso ;
• El parámetro -C son necesarios para que exporte el contenido respetando tildes, eñes, etc.

Esta exportación se realiza sin cabecera. Para incluir  en el fichero una línea de cabecera con el nombre de los campos habría que realizar las siguientes acciones:

1. Crear un fichero plantilla con la cabecera del fichero a generar.
   • Esta plantilla tendría que tener la línea de cabecera y una línea en blanco.
2. Exportar el fichero con la sintaxis explicada arriba
3. Copiar a la ruta final un fichero que sea la concatenación de la cabecera más el fichero exportado

Por tanto la sintaxis final sería:

Declare @vchSql as nvarchar(4000)Declare @bcp as nvarchar(4000)

Set @vchSql = 'Select * From TABLA' 
Set @bcp = 'bcp "'+@vchSql+'" queryout D:\Exp\exp.csv -T -t; -c' 

exec master..xp_cmdshell @bcp 

exec master..xp_cmdshell 'copy "D:\Exp\Template_header\template.csv" /B + "D:\Exp\exp.csv" /B \\serverDestino\carpetaDestino\export.csv'

NOTA: Es muy importante poner el parámetro /B en la copia/concatenación de los ficheros. Con este parámetro se fuerza a que la copia se haga en formato binario, de lo contrario lo copia en formato ASCII y por tanto incluye un salto de línea al final del fichero que hace que al importar el fichero generado con BULK INSERT falle.

PadLeft en Sql

Muy a menudo necesito utilizar una función similar a PadLeft en Sql, como esta función no está implementada en Sql Server, siempre tengo que buscar la "query" en internet.

Yo creo que la mejor opción es construir una función en Sql que pueda usar en cualquier momento.

Esta función Padleft lo que haría sería, rellenar con un carácter por la izquierda hasta completar el número de caracteres que le indiques por parámetro.

Aquí os dejo el código que he creado para construir esta función.
En este código, por optimización, distingo si la cadena original es numérica o alfanumérica.

ALTER FUNCTION PADLEFT
(
   @value as nvarchar(100),
   @charIzq as varchar(1),
   @intLenth as int
)
RETURNS nvarchar(100)
AS
BEGIN

   DECLARE @vchPadded as nvarchar(100)

   Set @vchPadded=@value

   If LEN(@value)< @intLenth
   Begin

      If ISNUMERIC(@value)=1
      Begin
         SELECT @vchPadded = 
           REPLACE(STR(@value, @intLenth), SPACE(1), @charIzq)
      End
      Else
      Begin
         Set @vchPadded=@value
         While (LEN(@vchPadded)<@intLenth)
         Begin
            Set @vchPadded=@charIzq + @vchPadded
         End

      End
   End

   RETURN @vchPadded

END 

Para usarla tendrías que escribir una consulta de este tipo:
  Select dbo.PADLEFT('63','0',5)
Su resultado sería: 00063

50 trucos de rendimiento Web

Hace unos días me llegó a través de geeks.ms  el enlace a un libro gratuito de 50 trucos de rendimiento Web y MVC.

Este libro es un pequeño manual que todos los programadores Web deberíamos tener como libro de cabecera, al que deberíamos consultar antes de diseñar una nueva Web, durante el desarrollo y a la hora de realizar las pruebas.

Los consejos de este libro tratan de todos los puntos a tener en cuenta tanto en el desarrollo como en el despliegue de la aplicación, ya que trata aspectos de configuración de IIS, modos de despliegue, trucos de desarrollo, etc, etc.

Instalar un servicio de Windows en una máquina de 64 bits

Si el PC con el que desarrollas es una máquina de 32 bits y el servidor en el que se va a instalar es de 64 bits puedes tener algunos problemas para realizar la instalación, ya que el instalador generado no funcionará.

Lo primero a tener en cuenta es que debes compilar todos los proyectos que incluyan el servicio, el propio servicio y todos los proyectos satélites, en x64. Esto se hace en la sección "Generar" de la ventana de propiedades de cada proyecto. Yo no lo dejaría en AnyCPU ya que no siempre funciona correctamente.

Pero con esto no es suficiente, ya que el instalador de Visual Studio, al menos hasta la versión 2008, tiene un  pequeño bug. Aunque la plataforma de destino sea x64, para generar el instalador Visual Studio usa una librería del sistema (InstallUtilLib.dll) compilada en 32 bits. Por tanto, al instalar el servicio en un servidor x64 se genera una excepción del tipo “BadImageFormat”.

Para solucionar este problema debes reemplazar la librería (InstallUtilLib.dll) introducida en 32bits  en el .msi instalador por la librería de 64bits.

Para reemplazar la librería debes seguir los siguientes pasos:

1. Abre el fichero .msi generado en Orca
1. Si no tienes instalada la aplicación Orca de Microsoft es necesario que la descargues e instales.
2. Selecciona “Binary table”
3. Haz doble click en la celda [Binary Data] para el registro “InstallUtil”
4. Asegúrate de que “Read binary from filename” está seleccionado  y pulsa sobre el botón “Browse”
5. Navega hasta la ruta %WINDIR%\Microsoft.NET\Framework64\v2.0.50727
1. Esta ruta solo está disponible en máquinas con arquitectura de 64Bits, por tanto debes seleccionar esta ruta en un servidor remoto, o copiar este fichero desde un servidor a tu máquina local.
6. Selecciona “InstallUtilLib.dll”
7. Pulsa en el botón “Open”
8. Pulsa en el botón “OK”
9. Guarda el .msi

Este .msi ya es posible instalarlo en un servidor de 64Bits.

Usar un fichero Excel desde un servicio de Windows

Si intentas manejar un fichero Excel con las librerías de interoperalilidad desde un servicio de Windows desarrollado en .Net, te encontrarás con una Excepción poco esclarecedora: "Microsoft Office Excel cannot access the file".

El problema se debe a que un servicio de Windows, independientemente del usuario de ejecución que se configure, se ejecuta en un perfil especial, el systemprofile. Este perfil no tiene una carpeta de escritorio, sin embargo la librería de interoperabilidad de Excel, para modificar un documento Excel, lo intenta abrir en segundo plano en el escritorio. Al no existir la ruta, se provoca esta excepción.

Por tanto, en el momento de la instalación es necesario crear la carpeta de escritorio (Desktop) en función de la arquitectura del servidor:

• Servidor de 32bits: %WINDIR%\System32\config\systemprofile\Desktop
• Servidor de 64bits: %WINDIR%\SysWOW64\config\systemprofile\Desktop