Question for SQL gurus ;)

General Help
1

I am a bit of an SQL moron, so could do with some help.

I’m using the callerid lookup module to search an SQL database using:

SELECT display_name FROM contacts WHERE office_phone LIKE ‘%[NUMBER]%’ OR cell_phone LIKE ‘%[NUMBER]%’ …

which works okay except that it returns the contact for the first blank field it finds - the first record in the database has a blank cell_phone entry :wink:

How can I tell it to ignore empty fields?

Cheers

Colin

2

Thanks for that … that is looking better. It is not selecting the first record anymore … just coming up with ‘unknown’ which in a way is better because it appears to be ignoring empty/blank entries.

Now I just have to work out why the %[NUMBER]% token isn’t matching anything :wink:

3

you mean how do u ignore #s that are blank?

SELECT display_name FROM contacts WHERE (office_phone LIKE ‘%[NUMBER]%’ OR cell_phone LIKE ‘%[NUMBER]%’) and (office_phone is not null or cell_phone is not null)

i usually work with ms sql… but that should work for mysql too

thanks
alex

4

Thanks for the info. Unfortunately that still doesn’t work.

Yeah the problem is that the first row in the database has an empty # for cell_phone so every call flags up the contact_name for the first row. You have got the problem right, but I tried your amended SQL command and I still get the same result.

:?

5

Also tried this

SELECT display_name FROM contacts WHERE (office_phone LIKE ‘%[NUMBER]%’ OR cell_phone LIKE ‘%[NUMBER]%’) AND (office_phone IS NOT NULL OR cell_phone IS NOT NULL)

No good.

6

… or even this

SELECT display_name FROM contacts WHERE (office_phone IS NOT NULL AND office_phone LIKE ‘%[NUMBER]%’) OR (cell_phone IS NOT NULL AND cell_phone LIKE ‘%[NUMBER]%’)

… don’t even know if that is valid SQL :wink:

7

Then the chances are that the field isn’t actually null - it’s blank.

Try the following:-

SELECT display_name FROM contacts WHERE (office phone <> ‘’ AND
office_phone LIKE ‘%[NUMBER]%’) or (cell_phone <> ‘’ AND office_phone
LIKE ‘%[NUMBER]%’)

If you find you’re getting NULL fields returned as well, you might try:-

SELECT display_name FROM contacts WHERE (office phone IS NOT NULL AND
office phone <> ‘’ AND office_phone LIKE ‘%[NUMBER]%’) or (cell_phone IS
NOT NULL AND cell_phone <> ‘’ AND office_phone LIKE ‘%[NUMBER]%’)

This SQL format should be valid for just about any database type.

colinjack wrote:

[quote] Also tried this

SELECT display_name FROM contacts WHERE (office_phone LIKE ‘%[NUMBER]%’ OR cell_phone LIKE ‘%[NUMBER]%’) AND (office_phone IS NOT NULL OR cell_phone IS NOT NULL)

No good.

[/quote]

Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net’s Techsay panel and you’ll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV

Amportal-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amportal-users

Post generated using Mail2Forum (http://www.mail2forum.com)