mysql - SQL: match a string pattern irrespective of it's case, whitespaces in a column -

i need find frequency of string in column, irrespective of case , white spaces.

for example, if string my tec bits , occur in table this, shown below :

061  mytecbits    12123 102  mytecbits    24324 103  tec bits  23432 247  tec bits  23243 355  tec bits  23424 454  tec bits  23432 

then, output should 6, because, whites pace removed , irrespective of case, strings identical.

is there grep() equivalent in sql there in r?

if concern space , case, need 2 functions:

• replace
• upper/lower

for example,

sql> data as(   2  select 'mytecbits' str dual union   3  select 'mytecbits' str dual union   4  select 'my tec bits' str dual union   5  select 'my tec bits' str dual union   6  select 'my tec bits' str dual union   7  select 'my tec bits' str dual   8  )   9  select upper(replace(str, ' ', '')) data  10  /  upper(repla ----------- mytecbits mytecbits mytecbits mytecbits mytecbits mytecbits  6 rows selected.  sql> 

then, output should 6

so, based on that, need use in filter predicate , count(*) rows returned:

sql> data as(   2  select 'mytecbits' str dual union   3  select 'mytecbits' str dual union   4  select 'my tec bits' str dual union   5  select 'my tec bits' str dual union   6  select 'my tec bits' str dual union   7  select 'my tec bits' str dual   8  )   9  select count(*) data  10  upper(replace(str, ' ', '')) = 'mytecbits'  11  /    count(*) ----------          6  sql> 

note with clause build sample table demonstration purpose. in our actual query, remove entire part, , use actual table_name in from clause.

so, need do:

select count(*) your_table    upper(replace(str, ' ', '')) = 'mytecbits' /