FUNÇÕES: Somente Números (com CTE)

[2011-04-02] Código completo:

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),
	@ZERO INT,
	@NOVE INT

SELECT
   @ZERO = UNICODE(0),
   @NOVE = UNICODE(9)

;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)
)
, NUMEROS AS
(
	SELECT LETRA
	FROM SPLIT
	WHERE UNICODE(LETRA) BETWEEN @ZERO AND @NOVE
)

SELECT @RESULTADO = (SELECT LETRA + '' FROM NUMEROS FOR XML PATH(''))
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[2011-04-02] Código completo v2:

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 += LETRA
FROM SPLIT
WHERE LETRA LIKE '[0-9]'
OPTION(MAXRECURSION 0)

SELECT @RESULTADO
[Post-Original] Explicação:

Boa noite pessoal!

Continuando o artigo anterior, vou demonstrar como recuperar somente os valores numéricos de um texto, agora com CTE.

Para começar, tenho o mesmo cenário do artigo anterior, uma variável com um texto absurdo, da qual desejo somente retornar os valores numéricos.

DECLARE @PALAVRA VARCHAR(8000)
SET @PALAVRA = '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'
</code></div>
Como estou utilizando CTE, não tenho necessidade de muitas variáveis.
<div style="border: 1px; background-color: #000000; color: #ffffff; font-weight: bold; border-color: #ffffff; margin: 5px; padding: 5px;"><code>DECLARE
@RESULTADO VARCHAR (8000),
@ZERO INT,
@NOVE INT

Mas ainda continuo a ter a necessidade das variáveis que delimitam o RANGE dos valores numéricos, no caso os ‘valores decimais’ dos caracteres 0 (zero) e 9 (nove):

SELECT
@ZERO = UNICODE(0),
@NOVE = UNICODE(9)

Com a primeira CTE, realizo crio a relação de um a um dos caracteres do meu texto (um caractere por linha). Na segunda CTE, aplico a validação do RANGE de cada um dos caracteres relacionados na primeira CTE.

;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@PALAVRA, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@PALAVRA, ID + 1, 1)
FROM SPLIT
WHERE ID &lt; LEN(@PALAVRA)
)
, NUMEROS AS
(
SELECT LETRA
FROM SPLIT
WHERE UNICODE(LETRA) BETWEEN @ZERO AND @NOVE
)

Por fim, utilizo uma query para concatenar os valores resultantes da segunda CTE, adicionando o HINT MAXRECURSION, para evitar que um texto com mais de 100 caracteres prejudique minha operação.

SELECT @RESULTADO = (SELECT LETRA + '' FROM NUMEROS FOR XML PATH(''))
OPTION(MAXRECURSION 8000)

No próximo artigo trarei a validação de CPF e CNPJ em uma única operação (e de forma otimizada).

Artigos relacionados:

CTE – Introdução:
https://sqlfromhell.wordpress.com/2009/09/20/cte-introducao/

FUNÇÕES: Somente Números (com WHILE):
https://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/

Anúncios

5 pensamentos sobre “FUNÇÕES: Somente Números (com CTE)

  1. Pingback: FUNÇÕES: Validação de CNPJ e CPF « Sql From Hell.com

  2. Pingback: Formatando valores numéricos, CPF e CNPJ « Sql From Hell.com

  3. Pingback: FUNÇÕES: Somente Números (com CTE) v2 « Sql From Hell.com

  4. Pingback: FUNÇÕES: Somente Números (com WHILE) « SQL From Hell.com

  5. Pingback: Como validar CPF e CNPJ no SQL Server | SQL From Hell.com

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s