rtrim - T-SQL trim & nbsp(y otros caracteres no alfanuméricos)
trim sql server 2012 (5)
Tenemos algunos datos de entrada que a veces aparecen con los caracteres & nbsp al final.
Los datos provienen del sistema de origen como varchar () y nuestros intentos de emitir como error decimal b / c de estos caracteres.
Ltrim y Rtrim no eliminan los caracteres, por lo que estamos obligados a hacer algo como:
UPDATE myTable
SET myColumn = replace(myColumn,char(160),'''')
WHERE charindex(char(160),myColumn) > 0
Esto funciona para & nbsp, pero ¿hay una buena manera de hacerlo para los caracteres no alfanuméricos (o en este caso numéricos)?
¿Cómo se está poblando la tabla? Si bien es posible eliminar esto en sql, un mejor enfoque sería cambiar el tipo de columna a int y restregar los datos antes de que se carguen en la base de datos (SSIS). ¿Es esta una opción?
Esta página tiene una muestra de cómo puede eliminar los caracteres no alfanuméricos:
-- Put something like this into a user function:
DECLARE @cString VARCHAR(32)
DECLARE @nPos INTEGER
SELECT @cString = ''90$%45623 *6%}~:@''
SELECT @nPos = PATINDEX(''%[^0-9]%'', @cString)
WHILE @nPos > 0
BEGIN
SELECT @cString = STUFF(@cString, @nPos, 1, '''')
SELECT @nPos = PATINDEX(''%[^0-9]%'', @cString)
END
SELECT @cString
Esto eliminará todos los personajes no alfanuméricos
CREATE FUNCTION [dbo].[fnRemoveBadCharacter]
(
@BadString nvarchar(20)
)
RETURNS nvarchar(20)
AS
BEGIN
DECLARE @nPos INTEGER
SELECT @nPos = PATINDEX(''%[^a-zA-Z0-9_]%'', @BadString)
WHILE @nPos > 0
BEGIN
SELECT @BadString = STUFF(@BadString, @nPos, 1, '''')
SELECT @nPos = PATINDEX(''%[^a-zA-Z0-9_]%'', @BadString)
END
RETURN @BadString
END
Use la función como:
UPDATE TableToUpdate
SET ColumnToUpdate = dbo.fnRemoveBadCharacter(ColumnToUpdate)
WHERE whatever
Para grandes conjuntos de datos, he tenido mejor suerte con esta función que verifica el valor ASCII. He agregado opciones para mantener únicamente alfa, numérico o alfanumérico en función de los parámetros.
--CleanType 1 - Remove all non alpanumeric
-- 2 - Remove only alpha
-- 3 - Remove only numeric
CREATE FUNCTION [dbo].[fnCleanString] (
@InputString varchar(8000)
, @CleanType int
, @LeaveSpaces bit
) RETURNS varchar(8000)
AS
BEGIN
-- // Declare variables
-- ===========================================================
DECLARE @Length int
, @CurLength int = 1
, @ReturnString varchar(8000)=''''
SELECT @Length = len(@InputString)
-- // Begin looping through each char checking ASCII value
-- ===========================================================
WHILE (@CurLength <= (@Length+1))
BEGIN
IF (ASCII(SUBSTRING(@InputString,@CurLength,1)) between 48 and 57 AND @CleanType in (1,3) )
or (ASCII(SUBSTRING(@InputString,@CurLength,1)) between 65 and 90 AND @CleanType in (1,2) )
or (ASCII(SUBSTRING(@InputString,@CurLength,1)) between 97 and 122 AND @CleanType in (1,2) )
or (ASCII(SUBSTRING(@InputString,@CurLength,1)) = 32 AND @LeaveSpaces = 1 )
BEGIN
SET @ReturnString = @ReturnString + SUBSTRING(@InputString,@CurLength,1)
END
SET @CurLength = @CurLength + 1
END
RETURN @ReturnString
END
Si el móvil puede comenzar con un signo más (+), usaré la función de esta manera
CREATE FUNCTION [dbo].[Mobile_NoAlpha](@Mobile VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @StartsWithPlus BIT = 0
--check if the mobile starts with a plus(+)
IF LEFT(@Mobile, 1) = ''+''
BEGIN
SET @StartsWithPlus = 1
--Take out the plus before using the regex to eliminate invalid characters
SET @Mobile = RIGHT(@Mobile, LEN(@Mobile)-1)
END
WHILE PatIndex(''%[^0-9]%'', @Mobile) > 0
SET @Mobile = Stuff(@Mobile, PatIndex(''%[^0-9]%'', @Mobile), 1, '''')
IF @StartsWithPlus = 1
SET @Mobile = ''+'' + @Mobile
RETURN @Mobile
END