none
ayuda con una query RRS feed

  • Pregunta

  • saludos tengo una query que me devuelve algo como esto

    34 000 BTU ó 34K BTU

    Lo que necesito es que cuando encuentre la palbra BTU adelante me recupere , el número 

    es decir 

    en ambos casos debe salirme 34,000, 

    No importa cuantos espacios delante este el BTU 

    jueves, 22 de septiembre de 2016 15:13

Respuestas

  • Le puse un acercamiento por el tema que hay que ver los caracteres no numericos que hay en la cadena para que no se caiga el bigint.

    Podemos usar una funcion y asi andaria,en la funcion le agrego la logica de sumar 000 si viene K

    declare @tabla table (campo varchar(50))
    insert into @tabla select '34 000 BTU'
    insert into @tabla select '34K BTU'
    insert into @tabla select '34.000 BTU'

    select convert(bigint,DBO.SOLONUMEROS(left(campo,charindex('btu',campo)-1))),* from @tabla


    alter FUNCTION SoloNumeros(@Texto VARCHAR(MAX))
    RETURNS VARCHAR(MAX) AS
    BEGIN
    DECLARE @CONTADOR SMALLINT,@RESULTADO VARCHAR(MAX),@CARACTER VARCHAR(1)
    SET @RESULTADO=''
    set @texto = replace(@texto,'K','000')
    SET @CONTADOR =1
    WHILE @CONTADOR <=LEN(@TEXTO)
    BEGIN
      SET @CARACTER=SUBSTRING(@TEXTO,@CONTADOR,1)
      IF ISNUMERIC(@CARACTER)=1 and @CARACTER !='.'
      BEGIN
        SET @RESULTADO = @RESULTADO+@CARACTER
      END
      SET @CONTADOR=@CONTADOR+1
    END
    RETURN @RESULTADO
    END

    viernes, 23 de septiembre de 2016 23:13

Todas las respuestas

  • Yo esto lo dividiría en dos partes. Por un lado haría un select con ... where campo like '%BTU%' para quedarme solo con los registros que contienen BTU. Si hay muchos registros, para evitar el tablescan es preferible activar el full-text indexing y buscar con ... where contains(campo, 'BTU').

    Una vez que de esa manera hemos obtenido los registros candidatos, el segundo paso es extraer la cifra numérica, que podría estar en el formato '34 000' o '34K' y presumiblemente otros tales como '34000' o '34.000', y puede ser antes o después de la palabra BTU. Eso ya no es labor para SQL Server, sino para un lenguaje cliente. Por ejemplo, en C# se podría lograr mediante la aplicación de varias Expresiones Regulares que busquen los distintos formatos soportados. Si no es factible hacerlo en el código cliente, y necesariamente se tiene que implementar en el servidor, este sería uno de los casos en los que merecería la pena inyectar una DLL en SQL Server (con CREATE ASSEMBLY...) y poner dentro de ella la función en C# que haga esa extracción.

    jueves, 22 de septiembre de 2016 18:00
  • Yo esto lo dividiría en dos partes. Por un lado haría un select con ... where campo like '%BTU%' para quedarme solo con los registros que contienen BTU. Si hay muchos registros, para evitar el tablescan es preferible activar el full-text indexing y buscar con ... where contains(campo, 'BTU').

    Una vez que de esa manera hemos obtenido los registros candidatos, el segundo paso es extraer la cifra numérica, que podría estar en el formato '34 000' o '34K' y presumiblemente otros tales como '34000' o '34.000', y puede ser antes o después de la palabra BTU. Eso ya no es labor para SQL Server, sino para un lenguaje cliente. Por ejemplo, en C# se podría lograr mediante la aplicación de varias Expresiones Regulares que busquen los distintos formatos soportados. Si no es factible hacerlo en el código cliente, y necesariamente se tiene que implementar en el servidor, este sería uno de los casos en los que merecería la pena inyectar una DLL en SQL Server (con CREATE ASSEMBLY...) y poner dentro de ella la función en C# que haga esa extracción.

    tiene que ser en sql , de ahi tiene que venir el dato, y no es un select exclusivo para una columna esta columna viene con otras, he visto qeu esto se logra extraer con fórmulas en excel ,asi que supuse que por SQL también
    jueves, 22 de septiembre de 2016 19:07
  • [...] he visto qeu esto se logra extraer con fórmulas en excel [...]

    El Excel contaría como una de las opciones de lo que yo he llamado "lenguaje cliente".

    La DLL de código gestionado montada en SQL Server es una opción que valdría para cualquier campo. Vista desde la sentencia se comporta como una función nativa, así que la llamada sería algo así como

    Select dbo.MiFuncion(miCampo) as Btu from MiTabla Where miCampo like '%BTU%'

    Finalmente, hacerlo en SQL, como poderse se podría, pero quedará bastante complicado.

    jueves, 22 de septiembre de 2016 19:45
  • La DLL de código gestionado montada en SQL Server es una opción que valdría para cualquier campo. Vista desde la sentencia se comporta como una función nativa, así que la llamada sería algo así como

    Select dbo.MiFuncion(miCampo) as Btu from MiTabla Where miCampo like '%BTU%'

    No entendí esto osea tengo que hacer un programa y pasarlo a sql

    Finalmente, hacerlo en SQL, como poderse se podría, pero quedará bastante complicado.

    Esto es lo que necesito

    jueves, 22 de septiembre de 2016 20:09
  • Un acercamiento

    declare @tabla table (campo varchar(50))
    insert into @tabla select '34 000 BTU'
    insert into @tabla select '34K BTU'
    insert into @tabla select '34.000 BTU'

    select convert(bigint,replace(replace(replace(left(campo,charindex('btu',campo)-1),' ',''),'K','000'),'.','')),* from @tabla

    jueves, 22 de septiembre de 2016 23:12
  • Un acercamiento

    declare @tabla table (campo varchar(50))
    insert into @tabla select '34 000 BTU'
    insert into @tabla select '34K BTU'
    insert into @tabla select '34.000 BTU'

    select convert(bigint,replace(replace(replace(left(campo,charindex('btu',campo)-1),' ',''),'K','000'),'.','')),* from @tabla

    hola LUIS TARZIA

    , gracias por tu respuesta , solo que porque le llamas un acercamiento?, hay algo que falte validar?

    veo que si pongo una cadena como esta '34_000 BTU' 

    se cae 

    Mens. 8114, Nivel 16, Estado 5, Línea 6
    Error al convertir el tipo de datos varchar a bigint.

    • Editado Augusto C viernes, 23 de septiembre de 2016 13:50
    viernes, 23 de septiembre de 2016 13:37
  • Le puse un acercamiento por el tema que hay que ver los caracteres no numericos que hay en la cadena para que no se caiga el bigint.

    Podemos usar una funcion y asi andaria,en la funcion le agrego la logica de sumar 000 si viene K

    declare @tabla table (campo varchar(50))
    insert into @tabla select '34 000 BTU'
    insert into @tabla select '34K BTU'
    insert into @tabla select '34.000 BTU'

    select convert(bigint,DBO.SOLONUMEROS(left(campo,charindex('btu',campo)-1))),* from @tabla


    alter FUNCTION SoloNumeros(@Texto VARCHAR(MAX))
    RETURNS VARCHAR(MAX) AS
    BEGIN
    DECLARE @CONTADOR SMALLINT,@RESULTADO VARCHAR(MAX),@CARACTER VARCHAR(1)
    SET @RESULTADO=''
    set @texto = replace(@texto,'K','000')
    SET @CONTADOR =1
    WHILE @CONTADOR <=LEN(@TEXTO)
    BEGIN
      SET @CARACTER=SUBSTRING(@TEXTO,@CONTADOR,1)
      IF ISNUMERIC(@CARACTER)=1 and @CARACTER !='.'
      BEGIN
        SET @RESULTADO = @RESULTADO+@CARACTER
      END
      SET @CONTADOR=@CONTADOR+1
    END
    RETURN @RESULTADO
    END

    viernes, 23 de septiembre de 2016 23:13
  • Hola Luis tarzia, problema con tu función

    si pones datos como estos,

    declare @tabla table (campo varchar(50))
    insert into @tabla select '38AUZ  34000 BTU NNNN'


    select convert(bigint,DBO.SOLONUMEROS(left(campo,charindex('btu',campo)-1))),* from @tabla

    junta los números 3834000

    aqui la clave es encontrar la palabra BTU
    • Editado Augusto C jueves, 6 de octubre de 2016 15:37
    jueves, 6 de octubre de 2016 15:36
  • La encuentra,el tema es que numeros tomas,el 34000 nada mas,es decir hasta un espacion adelante desde bu o las letras del  medio sirven ??

    En ese caso debe dar 34000 ??

    jueves, 6 de octubre de 2016 23:10
  • La encuentra,el tema es que numeros tomas,el 34000 nada mas,es decir hasta un espacion adelante desde bu o las letras del  medio sirven ??

    En ese caso debe dar 34000 ??

    Hola Luis,agradezco tu interés y si en efecto solo los números delante de BTU cuentan los demás números o letras no deben ser tomados en cuenta podemos tener algo asi,pueden estar un espacio o dos o tres o quizás mas

    38descripcion de un moto numero67475474  34000 BTU NNNN'

    y solo debe salir 34000

    lunes, 10 de octubre de 2016 15:31
  • Con esto solo tenemos el problema si dice 34 000 btu,lo estoy viendo pero salva los otros errores.

    select
    dbo.solonumeros(
    case charindex(' ',reverse(left(campo,charindex('btu',campo)-2)))
    when 0 then
    left(campo,charindex('btu',campo)-1)
    else

    substring(left(campo,charindex('btu',campo)-2),
    (len(left(campo,charindex('btu',campo)-2))-
    charindex(' ',reverse(left(campo,charindex('btu',campo)-2))))+2

    ,255) end),
    * from @tabla

    miércoles, 12 de octubre de 2016 0:09
  • Augusto C,

    Tengo una propuesta que resuelve los casos presentados sin embargo no hallo el criterio para tomar un sólo valor numérico cuando hay uno adyacente (separados por uno o más caracteres de espacio en blanco) tal como el último caso planteado, ¿cómo saber si el primer bloque de números no forma parte de un valor numérico junto con el segundo?, es decir:

    '34 000' /*UN VALOR NUMÉRICO*/
    '67475474  34000' /*¿UN SÓLO VALOR NUMÉRICO?*/


    WITH CTE AS
    (
        SELECT
    	   REVERSE(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(campo, 
    		  SUBSTRING(campo, PATINDEX('%BTU%', campo), 4000), '')
    		  , 'K', '|'), '_', '|'), '|', ''))) AS 'Cadena'
        FROM
    	   MiTabla
    )
    SELECT 
        REVERSE(SUBSTRING(Cadena, 1, ISNULL(NULLIF(PATINDEX('%[^0-9 .,]%', Cadena), 0), 
        LEN(Cadena) + 1) - 1)) 
    FROM 
        CTE;

    RESULTADO:

    Como vez el último caso no hallo el criterio para entender que son valores distintos, nota que en el primer caso también están separados sin embargo se asume "humanamente" que se trata de un único valor.


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

    miércoles, 12 de octubre de 2016 3:08
  • Augusto C,

    Tengo una propuesta que resuelve los casos presentados sin embargo no hallo el criterio para tomar un sólo valor numérico cuando hay uno adyacente (separados por uno o más caracteres de espacio en blanco) tal como el último caso planteado, ¿cómo saber si el primer bloque de números no forma parte de un valor numérico junto con el segundo?, es decir:

    '34 000' /*UN VALOR NUMÉRICO*/
    '67475474  34000' /*¿UN SÓLO VALOR NUMÉRICO?*/


    WITH CTE AS
    (
        SELECT
    	   REVERSE(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(campo, 
    		  SUBSTRING(campo, PATINDEX('%BTU%', campo), 4000), '')
    		  , 'K', '|'), '_', '|'), '|', ''))) AS 'Cadena'
        FROM
    	   MiTabla
    )
    SELECT 
        REVERSE(SUBSTRING(Cadena, 1, ISNULL(NULLIF(PATINDEX('%[^0-9 .,]%', Cadena), 0), 
        LEN(Cadena) + 1) - 1)) 
    FROM 
        CTE;

    RESULTADO:

    Como vez el último caso no hallo el criterio para entender que son valores distintos, nota que en el primer caso también están separados sin embargo se asume "humanamente" que se trata de un único valor.


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

    Hola gracias por la ayuda, lo que quiero es el primer valor numérico que se encuentre antes de la palabra BTU
    jueves, 13 de octubre de 2016 20:13
  • Este anda,solamente que si dice 34 000 te va tomar 000

    Con esto solo tenemos el problema si dice 34 000 btu,lo estoy viendo pero salva los otros errores.

    select
    dbo.solonumeros(
    case charindex(' ',reverse(left(campo,charindex('btu',campo)-2)))
    when 0 then
    left(campo,charindex('btu',campo)-1)
    else

    substring(left(campo,charindex('btu',campo)-2),
    (len(left(campo,charindex('btu',campo)-2))-
    charindex(' ',reverse(left(campo,charindex('btu',campo)-2))))+2

    ,255) end),
    * from @tabla

    jueves, 13 de octubre de 2016 23:10
  • Hola Luis Tarzia ,gracias por tus respuestas después de tiempo estoy retomando este tema.Disculpas por la demora en contestar , estoy poniendo la sentencia como debería ser .y obtengo este error

    Mens. 537, Nivel 16, Estado 3, Línea 43
    Invalid length parameter passed to the LEFT or SUBSTRING function.

    CommercialDescription es un campo de cadena o varchar(max) contiene algo como esto

    "CONDENSADORA LENNOX TSA060S4N44Y  PARTE PARA EQUIPO DE AIRE ACONDICIONADO UNIDAD 60,000 BTU 220/3PH/60HZ,"

    en cuyo caso la función debería rescatar el número 60,000, si quieres con coma o sin ella.

    select convert(bigint,DBO.SOLONUMEROS(left(CommercialDescription,charindex('btu',CommercialDescription)-1))),* from EXCEL_IMPORTACIONES



    • Editado Augusto C viernes, 9 de diciembre de 2016 17:20
    viernes, 9 de diciembre de 2016 17:15