SQL remove whitespace from Where Clause

Question

I have this where clause:

WHERE cc.community = 'Downtown'

but in my database there is no community called Downtown, but Down town. My question is can I remove the whitespace from the cc.community in my where clause its where Downtown = Downtown?

I wish I could, but Downtown and Down town is an example Downtown gets passed in via jquery based off the URL — user979331, May 30 '17 at 16:09

nacho · Accepted Answer · 2017-05-30T16:14:25.307

5

Try this:

 WHERE REPLACE(cc.community,' ','') = 'Downtown'

edited May 30 '17 at 16:14

answered May 30 '17 at 16:12

nacho

5,280
2
25
34

Those should be single quotes, not double quotes. `REPLACE(cc.community,' ','')` – Bacon Bits May 30 '17 at 16:13

score 1 · Answer 2 · answered May 30 '17 at 16:10

You can use something like

WHERE cc.community LIKE `Down%town`

The above should match both Downtown and Down town, but also things like Down_town or DownANYTHINGELSEtown. More precise matching is possible.

It will likely affect performance if your plan uses an index by the community field; check your query plan.

Gabri T · Answer 3 · 2017-05-30T16:12:45.340

0

update table set cc.community = 'Downtown' where cc.community like '%Down%town%'

this will change every row where there is the word Down and then town even if it is 42Down123town232

you can also change your "WHERE" clause to :

WHERE cc.community like 'Down%town' and it will work for both cases,but carefull...

it will also accept per example "Down_town" or "Down the town"

edited May 30 '17 at 16:12

answered May 30 '17 at 16:10

Gabri T

454
2
13

I dont want to update my table, I use the actual name somewhere else. – user979331 May 30 '17 at 16:10
check my second suggest then :) – Gabri T May 30 '17 at 16:14

SQL remove whitespace from Where Clause

3 Answers3