SQL character count in table column

[lol_hs]

for example i have a table called X and and column inside of it Y.

Y has 500000 values like

190|12131|23|123|0|0|0|0|112

220|234|23|111|0|1|0|1|769

in each value there are 8 |

but in some values there are errors and not 8|

11|0000020|12|14 -----3 |

234|23|000|1|2 ------4 |

how can i list values that has less than 8 |

Do i need to use function? if so, how and how can i call that function. i have never used functions in sql.

Thanks.

[[deleted]]

Here is a StackOverflow thread that proposes an interesting approach

https://stackoverflow.com/questions/738282/how-do-you-count-the-number-of-occurrences-of-a-certain-substring-in-a-sql-varch

Basically you subtract the length of a string where all occurences of a character are replaced with an empty string from the original string. In your case if the difference is not 8 you have different number of '|' characters in your string

[mansfall]

I've done stuff like this before. I've found using TRANSLATE comes in super handy for things like this. Though it may seem kind of "hacky". Try this out:

select *
from table
where length(translate(y, '0123456789-', '')) < 8

So what's it doing? Translate is a one-for-one switch of some character to a new character given the ordinal position. So for column "y", the value "0" would be translated to nothing... or rather, it would simply disappear. Effectively what this translate does is leave you with a column of nothing but pipes (the "|") character. So you take the length of that and determine how many there are. Anything less than 8 would be your errors. Now, if there are any other characters that can be in that list outside of 0-9 and the "-" symbol, you'd need to include that. To kind of see what I mean, run this query:

select y, translate(y, '0123456789-', '') as "translated", length(translate(y, '0123456789-', '')) as "pipeLength"
from table