1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

[SQL] Problem with using SUBSTRING and CHARINDEX

Discussão em 'Outras Linguagens' iniciado por Stack, Setembro 9, 2021.

  1. Stack

    Stack Membro Participativo

    I have a column (RCV1.ECCValue) in a table which 99% of the time has a constant string format- example being:

    T0-11.86-273

    the middle part of the two hyphens is a percentage. I'm using the below sql to obtain this figure which is working fine and returns 11.86 on the above example. when the data in that table is in above format

    'Percentage' = round(SUBSTRING(RCV1.ECCValue,CHARINDEX('-',RCV1.ECCValue)+1, CHARINDEX('-',RCV1.ECCValue,CHARINDEX('-',RCV1.ECCValue)+1) -CHARINDEX('-',RCV1.ECCValue)-1),2) ,


    However...this table is updated from an external source and very occasionally the separators differ, for example:

    T0-11.86_273

    when this occurs I get the error:

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    I'm very new to SQL and have got myself out of many challenges but this one has got me stuck. Any help would be mostly appreciated. Is there a better way to extract this percentage value?

    Continue reading...

Compartilhe esta Página