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

Question

I need to find the frequency of a string in a column, irrespective of its case and any white spaces.

For example, if my string is My Tec Bits and they occur in my table like this, as shown below :

061  MYTECBITS    12123
102  mytecbits    24324
103  MY TEC BITS  23432
247  my tec bits  23243
355  My Tec Bits  23424
454  My Tec BitS  23432

Then, the output should be 6, because, with whites pace removed and irrespective of case, all those strings are identical.

Is there any grep() equivalent in SQL as there is in R?

Answer 1

If your concern is only with the SPACE and the CASE, then you need two functions:

• REPLACE
• UPPER/LOWER

For example,

SQL> WITH DATA AS(
  2  SELECT 'MYTECBITS' STR FROM DUAL UNION ALL
  3  SELECT 'mytecbits' STR FROM DUAL UNION ALL
  4  SELECT 'MY TEC BITS' STR FROM DUAL UNION ALL
  5  SELECT 'my tec bits' STR FROM DUAL UNION ALL
  6  SELECT 'MY TEC BITS' STR FROM DUAL UNION ALL
  7  SELECT 'MY TEC BITS' STR FROM DUAL
  8  )
  9  SELECT UPPER(REPLACE(STR, ' ', '')) FROM DATA
 10  /

UPPER(REPLA
-----------
MYTECBITS
MYTECBITS
MYTECBITS
MYTECBITS
MYTECBITS
MYTECBITS

6 rows selected.

SQL>

Then, the output should be 6

So, based on that, you need to use it in the filter predicate and COUNT(*) the rows returned:

SQL> WITH DATA AS(
  2  SELECT 'MYTECBITS' STR FROM DUAL UNION ALL
  3  SELECT 'mytecbits' STR FROM DUAL UNION ALL
  4  SELECT 'MY TEC BITS' STR FROM DUAL UNION ALL
  5  SELECT 'my tec bits' STR FROM DUAL UNION ALL
  6  SELECT 'MY TEC BITS' STR FROM DUAL UNION ALL
  7  SELECT 'MY TEC BITS' STR FROM DUAL
  8  )
  9  SELECT COUNT(*) FROM DATA
 10  WHERE UPPER(REPLACE(STR, ' ', '')) = 'MYTECBITS'
 11  /

  COUNT(*)
----------
         6

SQL>

NOTE The WITH clause is only to build the sample table for demonstration purpose. In our actual query, remove the entire WITH part, and use your actual table_name in the FROM clause.

So, you just need to do:

SELECT COUNT(*) FROM YOUR_TABLE 
  WHERE UPPER(REPLACE(STR, ' ', '')) = 'MYTECBITS'
/

Answer 2

You could use something like

UPPER(REPLACE(userString, ' ', ''))

to check for upper case only and to remove white space.

Answer 3

You could cast your statements to LOWER() before comparing them eg.

LOWER(column_name) = LOWER(variable)

more specific:

LOWER(First_name) = LOWER('JoHn DoE')

would become first name = 'john doe'

For the spacing you should use replace, the format for that is:

REPLACE(yourstring, ' ' , '')

' ' = a space character replace it by an empty string = ''

So you would do

WHERE LOWER(REPLACE(fieldname, ' ', '') = 'mytecbits'

Answer 4

You need to use count to bring back the number affected, Lower will place the data into lower case so that when you make a comparison you can make it lower case.

To remove spaces you then use Replace and replace the space with an empty string for your comparison:

Select COUNT(ColumnA) 
from table 
where Lower(Replace(ColumnB, ' ', '')) = 'mytecbits'

Answer 5

If you are looking for the number of instances of one specific string, irrespective of case / whitespace, then you need to do the following -

• ignore whitespace

• ignore case

• count the number of instances of the string

So you want a query like the following -

SELECT 
    COUNT(field)
FROM
    table
WHERE
    UPPERCASE(REPLACE(field, ' ', '')) = UPPERCASE(REPLACE(userstring, ' ', ''))

This counts the number of rows in your table where field is the same as the userstring, when case is ignored (all set to the same case using UPPERCASE, so it is effecitvely ignored), and spaces are ignored (spaces are removed from the field and the userstring using REPLACE)

Answer 6

Since REGEXP is case insensitive, you can obtain a match by making the spaces optional, example:

SELECT count(field) FROM yourtable WHERE field REGEXP "MY *TEC *BITS";

Note: if needed, you can add a space or a [[:<:]] (word boundary) before "MY" and a space or a [[:>:]] after "BITS" to avoid false positive.