Conditional substring

Question

I have a table similar to:

--- ID ----  ErrorDescr ---
    1        Error: ERROR1 - ESK - motor problem
    1        Error: ERROR13 - EPN - window problem
    1        Human problem

What i want:

--ID--ErrorType---Count
  1   ESK            1
  1   EPN            1
  1   Human problem  1

if the "ErrorDescr" starts with Error:... the i want substring after "-" and get the 3 char error code, but if there is no Error:... I just take the text....and count the instances of those text...

Anyone ?

What have you tried so far? What problem did you encounter? Hint: look at `case when ... then .... else .... end `, `substring()` and `charindex()`. For the count, use this trick (multiplying by the length of the string replaced): https://stackoverflow.com/a/738296/361842 — JohnLBevan, May 30 '18 at 09:28
substring(ErrorDescr, LEN(LEFT(ErrorDescr, CHARINDEX ('-', ErrorDescr))) + 1, 4) as ErrorType.... — Joe Doe, May 30 '18 at 09:32
substring(ErrorDescr, LEN(LEFT(ErrorDescr, CHARINDEX ('-', ErrorDescr))) + 1, 4) as ErrorType....By using this I get the 3 char that i want for the ErrorType, but I have trouble to construct the case when there is no "-" in the text....And I have another problem (not clearly explained above) I have some ErrorDescr that have text like "Error: ERROR1 - Human problem" and here I shall not only that the first 3 char after - , because they dont start with 'E' (like the other 3 char codes).. — Joe Doe, May 30 '18 at 09:39
So...IF...the first char after '-' is E - then I shall take the 3 char — Joe Doe, May 30 '18 at 09:46
IF...the first char after '-' is NOT E - then I shall take the next 10 char — Joe Doe, May 30 '18 at 09:47
if you hit [EDIT](https://stackoverflow.com/posts/50600795/edit) on your question you can add this additional info there; that way you'll have more formatting options to make this readable, and others will see this information in your question without having to head to the comments. — JohnLBevan, May 30 '18 at 09:51
Also you mention you're using SQL; is that MS SQL, or MySQL, etc? — JohnLBevan, May 30 '18 at 09:51

JohnLBevan · Answer 1 · 2018-05-30T10:13:42.220

Try this: SQL Fiddle

select Id
, ErrorType
, (len(ErrorDescr) - len(replace(ErrorDescr, ErrorType, '')))/nullif(len(ErrorType),0) Count
from 
(
    select Id
    , ErrorDescr
    , case 
        when ErrorDescr like 'Error:%- E__ -%' then
            substring(ErrorDescr, charindex('-',ErrorDescr)+2, 3)
        else 
            ErrorDescr
     end ErrorType
     from Error
 ) subQuery

What's going on?

The case statement allows us to choose the appropriate logic for handling those lines beginning Error: vs those which don't. I've used the expression ErrorDescr like 'Error:%- E__ -%' to ensure that as well as beginning Error:, the string should also contain a hyphen followed by a space then a 3 char code starting E before another space and hyphen, then whatever.
To get the substring we just use the substring function: substring(ErrorDescr, charindex('-',ErrorDescr)+2, 3). We find the position of the first hyphen, then move forwards two places to find the space, then the E (the first char of our 3 char code). Starting at this character (E) we take 3 characters, getting our full code.
We put this in a subquery so that our code is available to us in the outer query, rather than us having to recalculate this value in order to calculate the count. The code to count the occurrences of the code in the description is: (len(ErrorDescr) - len(replace(ErrorDescr, ErrorType, '')))/nullif(len(ErrorType),0). Here we take the difference between the full description's length and the length of the description with all instances of the code removed to get a value representing the number of characters removed. We then divide this by the number of characters in the code to get the number of codes removed. e.g. if we have a string of 12 characters containing 2 instances of a 3 char code we'd have (12 - 6) / 3, giving us 2. The nullif is included so that if the code were somehow 0 (possible if ErrorDescr is blank, or if the code to capture the error code were amended), we'd avoid a divide 0 error, instead saying the count is null when there are no codes to count.

Update

Regarding taking 10 chars when the code doesn't start with an E, you can amend your case statement like this: SQL Fiddle

, case 
   when ErrorDescr like 'Error:%- E__ -%' then
       substring(ErrorDescr, charindex('-',ErrorDescr)+2, 3)
   when ErrorDescr like 'Error:%- %' then
       substring(ErrorDescr, charindex('-',ErrorDescr)+2, 10)
   else 
       ErrorDescr
end ErrorType

i.e. The scenario where the code starts with E is handled by the first when statement; so for those values the second when (which would also match those values) isn't hit; but those values beginning Error: which don't have an E code fall through to this second one and get handled here, taking the 10 characters. Any which match neither fall through to the else statement and the full string is returned.

Thanks John :) But what if I want the result to be a table with the columns: ID---ESK---EPN--Human Problem-- and rows : 1-- 1--1--1, is that possible ? — Joe Doe, May 30 '18 at 10:59
Yes - for that you'd want some kind of pivot table / conditional aggregation. See this question for an example of such. https://stackoverflow.com/a/50532729/361842 If you don't know the possible values / column names until runtime you'd need to go the dynamic SQL route... but generally I'd advise against working with an unknown number of columns, since it'll come back to bite you elsewhere in your code. — JohnLBevan, May 30 '18 at 12:05

Conditional substring

1 Answers1

Update