[Ajuda] Substring SQL Server 2000

[krabby]

Boa tarde,

Estou a executar esta query:

Select substring(mMsgContent, charindex ('total registos', mmsgcontent), charindex ('Valor Total', mmsgcontent) - charindex ('total registos', mmsgcontent)) 
from tblevent with (nolock)
where tfrom like '%XPTO%' and dcdservertime > '2008-11-03'

Onde o devolve, é um número inteiro, retirado de um pedaço de texto.

A ideia seria somar todos os números devolvidos, resultantes da condição definida na query (Data).

Quando executo assim como está, surge a seguinte mensagem de erro:

Invalid length parameter passed to the sub
string function.

Alguem me consegue ajudar com isto? Não percebo muito de SQL, mas tou a tentar aprender mais qualquer coisa!.

Obrigado desde Já!

 

[JGAlmeida]

Isso está um bocado confuso.

O campo mMsgContent contem o texto "total registos" com um numero concatenado?

 

[krabby]

O resultado da query inicial è:

Total Registos: 3
Total Registos: 46
Total Registos: 1
Total Registos: 45
Total Registos: 2
Total Registos: 76
Total Registos: 1
Total Registos: 13
(9 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

 

[krabby]

Isto basicamente devolve-me o numero de registos de todos os dados que respeitem a seguinte condição:

tfrom like '%XPTO%' and dcdservertime > '2008-11-03'

O Problema é que esses dados são bem mais que 9 e a query só dá os primeiro 9 e depois dá o erro mostrado acima.

Cumps!

 

[JGAlmeida]

Então vê lá se ele assim te dá uma lista de valores:

select substring(mMsgContent, charindex ('total registos: ', mMsgContent), LEN(mMsgContent)) 
from tblevent with (nolock)
where tfrom like '%XPTO%' and dcdservertime > '2008-11-03'

 

[krabby]

Da forma como puseste, ocorre este erro!

Server: Msg 8116, Level 16, State 2, Line 1
Argument data type ntext is invalid for argument 1 of len function.
Server: Msg 8116, Level 16, State 1, Line 1
Argument data type void type is invalid for argument 3 of substring function.

cumps

 

[JGAlmeida]

O tipo de dados desse campo é ntext?

EDIT:

Embora não ache muito boa ideia esse campo ser ntext em vez de varchar, há maneira de conseguir o que queres.

Assumindo que o texto antes do valor é sempre 'total registos: '

1º converter o campo mMsgContent para nvarchar.
2º substring desse nvarchar a começar na posição 17 e comprimento uns 10 (ou o tamanho máximo do valor que vais buscar).
3º convert isso para float.
4º sum a isso tudo.

o que dá:

SELECT sum(convert(float, substring(convert(nvarchar, mMsgContent),17,10)))
from tblevent 
where ...

 

[krabby]

O comprimento e a posição diferem sempre, por isso é que estava a utilizar o substring dessa forma.

Ou seja, a coluna em questão, está preenchida como uma mensagem tipo email Onde o conteúdo è +- o seguinte:

"Número de registos: 50 Valor Total: 100."

Assim sendo, o que eu preciso é obter o valor que está entre "Número de Registos" e "Valor Total" que no exemplo que dei é 50, mas que pode ser 5000 ou 500000...

Depois de conseguir obter todos os valores, englobados pela condição "Where", pretendia somá-los.

Eu tenho muitas lacunas em SQL e está a ser muito dificil. MAS NÃO DESISTO loool

Cumps!

 

[krabby]

[COLOR=blue]select[/COLOR] substring (mMsgContent, charindex ('total registos', mmsgcontent), charindex ('Valor Total', mmsgcontent) - charindex ('total registos', mmsgcontent))
[COLOR=blue]from[/COLOR] tblevent with (nolock)
[COLOR=blue]where[/COLOR] tfrom like  [EMAIL="'%[email protected]%'"]'XPTO'[/EMAIL] and dcdservertime > '2006-12-29' and dcdservertime < '2007-12-29'

Com esta Query eu consido obter os valores para tudo o que é englobado pela condição Where. O resultado, mostra os seguintes:

Total Registos: 7
Total Registos: 8
Total Registos: 3
Total Registos: 32
Total Registos: 251
Total Registos: 4
Total Registos: 28
Total Registos: 16
Total Registos: 19
Total Registos: 11
Total Registos: 2
Total Registos: 2
Total Registos: 3


Assim, o que eu pretendia era somar os valores apresentados, mas tinha ainda de retirar apenas o caracteres numéricos para depois poder somá-los. Isso é o passo seguinte que me está a moer a cabeça!

Cumps

 

[JGAlmeida]

Então tenta lá assim:

SELECT sum(convert(float, substring( convert(nvarchar, substring (mMsgContent, charindex ('total registos', mMsgContent), charindex ('Valor Total', mMsgContent) - charindex ('total registos', mMsgContent))), 17, 10))) 
from tblevent 
where ...

 

[krabby]

Então tenta lá assim:

SELECT sum(convert(float, substring( convert(nvarchar, substring (mMsgContent, charindex ('total registos', mMsgContent), charindex ('Valor Total', mMsgContent) - charindex ('total registos', mMsgContent))), 17, 10))) 
from tblevent 
where ...

Surge esta mensagem de erro:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

Cumps!

 

[JGAlmeida]

Tenta lá na query que mandei mudar o substring( ... ,17,...) para substring( ... ,21,...).

 

[krabby]

Desculpa, mas o erro é mesmo com o 21!!

cumps!

 

[krabby]

Testei com substring( ... ,23,...), só como teste e deu como resultado:

0.0

Cumps!

 

[JGAlmeida]

A logica é a seguinte:

com a tu query

substring (mMsgContent, 
  charindex ('total registos', mMsgContent), 
  charindex ('Valor Total', mMsgContent) - charindex ('total registos', mMsgContent))

consegues retornar uma lista com uma frase de comprimento fixo concatenada com o numero:

Total Registos: 7
Total Registos: 8
Total Registos: 3
Total Registos: 32

vais buscar o substring dessa lista a começar na posição do numero (no exemplo seria 17) e um comprimento qualquer.

Isso devia dar-te uma lista com apenas os numeros (mas ainda string). Depois é converter para float e somar tudo, ou seja :

sum(convert(float, substring( [a tua 1ª query] 17, 10)))

Agora é uma questão de fazeres experiências até acertares. Sem saber melhor os dados com que estás a trabalhar não dá para fazer muito mais que isso.

 

[krabby]

Testei com substring( ... ,17,1), em vez do substring( ... ,17,10), e o que acontece é que ele apenas faz a soma do primeiro digito do valor que aparece.

O resultado foi 266.00!

Eu estive a fazer a conta manualmente, e realmente é isso.

Se alterar o 1 por outro para abranger mais dígitos ocorre aquele erro. Será porque nem todos os valores são iguais em numero de digitos? porque 1 digito todos tem, mais que 1 nem todos.

Cumps!

 

[JGAlmeida]

Estranho, eu para testar criei uma tabela com um campo ntext e ele não falhou nisso, fez bem as contas...

 

[krabby]

Estranho, eu para testar criei uma tabela com um campo ntext e ele não falhou nisso, fez bem as contas...

Ok, de qualquer forma obrigado. Vou continuar a fazer testes para ver se acerto com isto!

Se há coisas teimosas, esta é uma delas.

Cumprimentos!

 

[krabby]

Estive a verificar, e se na cláusula Where, estiver uma condição que devolva só valores com 3 digitos por exemplo, eu possso colocar na query substring( ... ,17,3), que o valor devolvido é o correcto, mas se colocar na cláusula where 3 valores em que um tenha 3 digitos e ou outro apenas 2, dá erro.

Agora não sei como vou resolver isto.

Cumps!

 

[krabby]

Boas,

À partida esta situação acontece, porque é detectado o caractere terminador. quando o detecta dá erro.

Alguem sabe como posso ignorar esse caractere? ou colocá-lo com limite?

Do genero, lê o número até encontrar o limitador.

Cumps!