CODEWITHSUNDEEP
sqlsql-server
Paul Young
Paul Young

Reputation: 3

Problem with using SUBSTRING and CHARINDEX

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?

Upvotes: 0

Views: 302

Answers (3)

SAI PAVAN
SAI PAVAN

Reputation: 64

Replace '_' with '-' to string in CHARINDEX while specifying length to the substring

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

Upvotes: 2

Charlieface
Charlieface

Reputation: 71159

Use NULLIF to null out such values

round(
  SUBSTRING(
    RCV1.ECCValue,
    NULLIF(CHARINDEX('-', RCV1.ECCValue), 0) + 1,
    NULLIF(CHARINDEX('-',
              RCV1.ECCValue,
              NULLIF(CHARINDEX('-', RCV1.ECCValue), 0) + 1
       ), 0)
       - NULLIF(CHARINDEX('-', RCV1.ECCValue), 0) - 1
  ),
2)

I strongly recommend that you place the repeated values in CROSS APPLY (VALUES to avoid having to repeat yourself. And do use whitespace, it's free.

Upvotes: 0

Rajat
Rajat

Reputation: 5803

If you can guarantee the structure of these strings, you can try parsename

select round(parsename(translate(replace('T0-11.86_273','.',''),'-_','..'),2), 2)/100

Breakdown of steps

  • Replace . character in the percentage value with empty string using replace.
  • Replace - or _, whichever is present, with . using translate.
  • Parse the second element using parsename.
  • Round it up to 2 digits, which will also automatically cast it to the desired numeric type.
  • Divide by 100 to restore the number as percentage.

Documentation & Gotchas

Upvotes: 0

Related Questions