[2011-04-02] Código completo – Somente números:
DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'
DECLARE @RESULTADO VARCHAR(8000)
SET @RESULTADO = ''
;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
FROM SPLIT
WHERE ID < LEN(@TEXTO)
)
SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[0-9]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)
SELECT @RESULTADO
[2011-04-02] Código completo – Somente texto:
DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'
DECLARE @RESULTADO VARCHAR(8000)
SET @RESULTADO = ''
;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
FROM SPLIT
WHERE ID < LEN(@TEXTO)
)
SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[A-z]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)
SELECT @RESULTADO
[2011-04-02] Código completo – Remover caracteres especiais:
DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'
DECLARE @RESULTADO VARCHAR(8000)
SET @RESULTADO = ''
;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
FROM SPLIT
WHERE ID < LEN(@TEXTO)
)
SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[A-z0-9]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)
SELECT @RESULTADO
[Post-Original] Explicação:
Boa noite pessoas!
Já faz algum tempo que escrevi as funções “somente números” com WHILE e CTE, até que estes dias eu tive de reformular elas de forma a remover caracteres especiais, remover máscaras e afins, assim como para outras funcionalidades mais absurdas… O legal foi que causou uma leve mudança da estrutura original da minha CTE antiga, resultando numa terceira função “somente números” bem interessante, que facilmente pode ser tranformada em “somente letras”, “somente números e letras”, “somente caractéres internacionais”… Ok, famos ao que interessa!
Primeiramente temos o texto:
DECLARE @Texto NVARCHAR(MAX)
SET @Texto = N'O azul é uma das três cores-luz primárias,
e cor-pigmento secundária, resultado da sobreposição dos
pigmentos ciano e magenta. Seu comprimento de onda é da
ordem de 455 a 492 nanômetros do espectro de cores visíveis.';
Em seguida uma variável que receberá o resultado:
DECLARE @Result NVARCHAR(MAX)
SET @Result = ''
E a expressão/CTE de split de caractere por caractere:
;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@Texto, 1, 1) AS CH
UNION ALL
SELECT ID + 1, SUBSTRING(@Texto, ID + 1, 1)
FROM SPLIT
WHERE ID < LEN(@Texto)
)
E agora para filtrar e juntar todos os caracteres:
SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[0-9]'
OPTION (MAXRECURSION 0)
Enfim exibindo o resultado:
SELECT @Result AS [Resultado]
Também é possível adaptar a consulta para “somente letras” ou “somente alfa-numéricos” (removendo os caracteres especiais):
SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[A-z]'
OPTION (MAXRECURSION 0)
-- OU
SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[A-z0-9]'
OPTION (MAXRECURSION 0)
Mas estas consultas também podem ter mais utilidade com um CASE substituindo os caracteres especiais por espaço ou outro caractere qualquer:
SELECT @Result = @Result
+ (CASE WHEN CH LIKE '[A-z0-9]' THEN CH ELSE '' END)
FROM SPLIT
OPTION (MAXRECURSION 0)
Mas sempre é bom lembrar que é melhor ter rotinas deste gênero na aplicação no lugar do banco de dados ou pensar em CLR.
Obrigado Eduardo Ruthes pela idéia inicial desta CTE!!!
Artigos relacionados:
FUNÇÕES: Validação de CNPJ e CPF
https://sqlfromhell.wordpress.com/2009/11/19/validacao-de-cnpj-e-cpf/
FUNÇÕES: Somente Números (com CTE) [Original]
https://sqlfromhell.wordpress.com/2009/11/18/somente-numeros-com-cte/
FUNÇÕES: Somente Números (com WHILE)
https://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/
SQL From Hell.com 