Comparing strings with spaces and different cases

Databases
1

I would like to run a query on my database to check if a postcode exists in the database.

The postcode is generally of the format

BA21 9XY

However it may be written as:

ba21 9xy (LOWERCASE)
BA219XY (NO SPACES)

I have currently got the following that deals with the case difference but can’t figure out how to deal with spaces. Trim/RTrim/LTrim appears to only remove trailing and leading spaces.



$query="SELECT postcode FROM newBusiness WHERE UPPER(postcode) = UPPER('$postcode')";

Does the following look like a viable solution?



$query="SELECT postcode FROM newBusiness WHERE UPPER(REPLACE(postcode, ' ', '')) = UPPER(REPLACE('$postcode',' ',''))";

Thanks in advance

Paul

2

Perhaps a better solution would be to amend the processing that writes the data into the database to convert it to a standard format at that point. Then you just need to run a one time update of the database to convert the data in the column to that format. Once that is done you’d know that they are all in that format when doing the comparisons and hence the comparisons will be easier to write and faster to run.

3

let me answer this by asking what happened when you tested it?

:wink:

4

Thanks Rudy - very helpful reply.

I did originally just use "WHERE postcode =‘$postcode’ then realised that some people had entered their postcodes with no spaces and also some in lowercase. I will do as you suggest and ensure all postcodes entered in the database are set to uppercase and look into doing some sort of client side validation to check for lack of spaces in the postcode.

Thanks again for your very helpful advice.

5

unfortunately your code is already the most efficient it’s gonna get, which isn’t very

to make it more efficient, you have to make the query sargable, which simply means you need to write the query in such a way as to avoid using any functions on the column value

so instead of

… WHERE UPPER(REPLACE(postcode, ’ ', ‘’)) = ‘something’

you want to write

… WHERE postcode = ‘something’

and then an index on the postcode column can be utilized to optimize the query

the only way this is going to happen is if you store the data with the UPPER and REPLACE functions already having been applied

this confirms one of the maxims of data design: perform cleansing functions on data on the way in (INSERTs) instead of on the way out (SELECTs)

6

Sorry, should have phrased my post differently.

The code I listed did appear to work however I am wondering if it is the most efficient code to use. I have read about using REGEX and LIKE statements and therefore was thinking the code I have tried is likely not to be the most efficent query:)