CODEWITHSUNDEEP
sqlmariadb
Ishu Verma
Ishu Verma

Reputation: 51

SQL query to find a partial string match that could include special characters

SQL query with special character ()

The original query (big thanks to GMB) can find any items in address (users table) that have a match in address (address_effect table).

The query works fine if address contains ',' but I can't seem to make it work if there is '()' in the address field.

Here is the sql query that's not working:

UPDATE users u 
SET u.COUNT = (
  SELECT COUNT(*) FROM address_effect a 
  WHERE FIND_IN_SET(a.address, REPLACE(u.address, ', ', ','')')) 
)

Fyi, I'm testing this on my local system with XAMPP (using MariaDB).

I tried to identify '()' as an escape character by prepending it with backslash '' but it doesn't help.

user table

+--------+-------------+---------------+--------------------------+--------+
|    ID  |  firstname  |  lastname     |    address               |  count |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     1  |    john     |    doe        |james street, idaho, usa  |        |                    
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     2  |    cindy    |   smith       |rollingwood av,lyn, canada|        |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     3  |    rita     |   chatsworth  |arajo ct, alameda, cali   |        |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     4  |    randy    |   plies       |smith spring, lima, (peru)|        |                       
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     5  |    Matt     |   gwalio      |park lane, (atlanta), usa |        |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+

address_effect table

+---------+----------------+
|address   |effect         |
+---------+----------------+
|idaho    |potato, tater   |
+--------------------------+
|canada   |cold, tundra    |
+--------------------------+
|fremont  | crowded        |
+--------------------------+
|peru     |alpaca          |
+--------------------------+
|atlanta  |peach, cnn      |
+--------------------------+
|usa      |big, hard       |
+--------+-----------------+

Upvotes: 0

Views: 508

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

I would suggest using regular expressions for this. It seems more general than fiddling with the string:

update users u
    set count = (select count(*)
                 from address_effect ae
                 where u.address regexp concat('[[:<:]]', ae.address, '[[:>:]]'))
                );

The funky character class is MySQL's way of delineating a word boundary (I am more used to \W but MySQL doesn't support that).

Here is a db<>fiddle.

Upvotes: 2

forpas
forpas

Reputation: 164139

Just like you replace the space after each comma with just a comma, use REPLACE() to remove the chars '(' and ')':

FIND_IN_SET(a.address, REPLACE(REPLACE(REPLACE(u.address, ', ', ','), '(', ''), ')', ''))

See the demo.
Results:

| ID  | firstname | lastname   | address                    | count |
| --- | --------- | ---------- | -------------------------- | ----- |
| 1   | john      | doe        | james street, idaho, usa   | 2     |
| 2   | cindy     | smith      | rollingwood av,lyn, canada | 1     |
| 3   | rita      | chatsworth | arajo ct, alameda, cali    | 0     |
| 4   | randy     | plies      | smith spring, lima, (peru) | 1     |
| 5   | Matt      | gwalio     | park lane, (atlanta), usa  | 2     |

Upvotes: 1

Related Questions