Can I use trim in a sql query condition

Question

Hi I am creating a reverse phone number look up in my database. I have a box that the user enters a phone number (in my example the number is 01772708200 NO SPACES etc) What I want to do is query my sql database and return matching results. The problem is my database contains thousands of numbers in different formats (I should have formated this at the start but I did not) and potentially the numbers could be represented as 01772 708200 or 01772 708 200 etc)

I have included a bit of script which I thought would trim the condition for the search but it does not work ;

    $value="01772708200" ; 
    $condition = "TRIM(phone1) LIKE '%$value%' ";
    $result = mysql_query("SELECT * FROM major WHERE $condition ")  ;

value is the number entered by the user and just shown here as a variable for clarity .
phone1 is the phone number in my db table called major .

Can anyone suggest what I am doing wrong please ?

thanks

Answer 1

TRIM() only removes leading and trailing spaces, so 01772 708200 will still remain the same. You would need to use REPLACE().

You might also be able to cook up something that disregards additional ()-using the REGEXP operator.

Answer 2

you'll probably want to do something like

$condition = " REPLACE(phone1, ' ', '') LIKE '%$value%'";

Answer 3

Trim only removes spaces to the right and left. Use:

 REPLACE(REPLACE(col1, ' ', ''),'\t','')

To replace all instances of spaces and tabs.

(By the way, do Google search for SQL Injection. :))

Answer 4

TRIM removes only leading and trailing spaces. To get rid of spaces in the middle, you'd want to use REPLACE instead.

REPLACE(phone1, ' ', '')

Answer 5

The answer to the question is: yes, you can and sometimes you have to.

If there wasn't any scrubbing on the data before it went into the database and contains leading or trailing spaces, then you'd want to if you're searching on that term without the wildcard:

SELECT  name
FROM    tbl
WHERE   trim(name)='foo'

If the name was 'foo ', this would not work w/o the trim()

---

For your particular problem:

It works as intended, but you'd want to use replace() or regexp_replace(), depending on what database you're using and what the actual data looks like.

• Trim is used to "trim" off the spaces at the beginning and end of a word boundary
• In some databases you need to use btrim() to trim the front and back
• In older databases you might have to use a combination of ltrim() and rtrim() to trim off the left and right side, respectively

Answer 6

Well, TRIM won't help you as it only removes spaces at the beginning or end of the string. You could replace $condition for:

$condition = 'REPLACE(REPLACE(REPLACE(phone1,"/",""),"-","")," ","")';

You could nest more of these so it replaces more than "/", "-" and " ".

Answer 7

For those that are reading this and are using MSSQL, I found this useful:

http://www.devcha.com/2007/04/ms-sql-trim-function.html

(There is no TRIM function in MSSQL, but using RTRIM and LTRIM does the trick)