CODEWITHSUNDEEP
sql
Rengar
Rengar

Reputation: 1

SQL character count in table column

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.

Upvotes: 0

Views: 68

Answers (2)

Florian Lim
Florian Lim

Reputation: 5362

Instead of counting the occurence of |, you could compare the length of the string with a string that has no |:

select * 
  from X
  where length(Y) <> (length(replace(Y, '|', '')) + 8)

Not sure if this is faster than John's answer.

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81930

Assuming max 8 |

Not elegant, nor sargible

Select * 
 from  X 
 Where Y not like '%|%|%|%|%|%|%|%|%'

Upvotes: 2

Related Questions