Jump to content

Please - help with sql query needed


Recommended Posts

Hi,

I want to query a firebird database. Everything works fine, but i need to optimize my query so the results will be ok.

Each row of my table holds a customer with collumns like lastname, firstname, phone1, phone2, fax and mobile

My script has a phonenumber and i want to find alle customers with that phonenumber (yes, the number can be used by more than one customer).

Right now i use something like: $sQUERY = 'Select * from ' & $sTableName & " WHERE PHONE1 LIKE '%" & $number & "%'"

As long as the number is stored correctly it works fine, but sometimes there are spaces or comments in the stored number or a number is written in different ways like 089-123456 or like 089/123456 or like 089123456.

How can i construct a query to only compare the numeric part (that means strip letters and whitespaces) of the phone1 column (and also the other phone columns)? Only using wildcards like % and _ (that is waht i do now) is probably not enough. I need something a bit more complicated :-)

On the web i found examples using substring with something like %[0-9]%  but i am not sure if this syntax is supported by the firebird db and even if it is, i did not manage to get it working.

Can anyone help me here? Thanks!

 

Edited by Allow2010
Link to post
Share on other sites

thank you, but this is the opposite of what i am looking for. I have the phone number in a correct format only containing numbers, but in the database the number can be stored in different ways, so the sql query needs to filter whitespaces and letters, which is not done in autoit code, but in sql syntax...i need help from someone who ist good at sql and not at autoit :-)

Link to post
Share on other sites

I think that before you save the number in your database you'll need to strip down the number input using StringReplace like:

$number = StringReplace($number, "/", "")
_SQLite_Exec($sqlHandle, "INSERT INTO " & $tableName & " (name,age,isNumber,parentId) VALUES ('" & $name & "','" & $age & "','" & $number & "','" & $parentID & "')")

That will actually lessen the hassle of removing unnecessary symbols on the input.

(I only use SQLite im sorry if that bothers you. )

;) ~XN~ ;)

Link to post
Share on other sites

Hi, thanks, but this is still not what i am looking for, i want to find a number in the database, i do not want to store a number (the numbers will be stored by different users with a different app, i can not change this).

In the DB there are numbers in differen formats, like 089-123456 or 089123456 or 089 123456

I always have my numer in the format 08912345 so i do NOT need to do anything with my number in autoit. I need an sql query that will match my number with any number in the db that ist like my number, ignoring the non numeric characters. This has to be part of the query, it can not be done by autoit...this is no autoit question, it is an sql question.

Edited by Allow2010
Link to post
Share on other sites

I see where you're coming from...

first you should create a UDF like this:

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

then use the function as 

SELECT dbo.udf_GetNumeric(column_name) 
from table_name

This should wrap things up.

However, having firebird in mind, first thing that comes to mind is:

SET TERM ^ ;

create or alter procedure GET_DIGIT_ONLY (
    IPARAM varchar(32))
returns (
    OPARAM varchar(32))
as
declare variable I integer;
begin
  oparam = '';
  i = 1;
  while (i <= char_length(:iparam)) do
  begin
    if (substring(:iparam from i for 1) similar to '[0123456789]')  then
      oparam = :oparam || (substring(:iparam from i for 1));
    i = :i + 1;
  end
  suspend;
end^

SET TERM ; ^

and use it like these:

execute procedure get_digit_only :input_param
returning_values :output_param

OR 

select get_digit_only.oparam from get_digit_only ('393SEA981F')

 

;) ~XN~ ;)

Link to post
Share on other sites

This is a silly misuse of a RDBMS engine. Letting garbage in implies a mess in applications to sort out wanted gems.

What you could eventually do is add the table a new column in last position and store there the numbers in searchable form (e.g. digits only), then add triggers on insert and update to populate this extra column when rows are added/changed. Of course adding a suitable index on this column could help if the table is large enough. An alternative would be to create a new table to hold IDs of offending table and clean numbers.

Clearly this requires you have admin rights on the DB.

If you were using SQLite you could make use of a loadable extension to use a filtering function (I have a strfilter function), but this is silly as well.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
14 hours ago, XaelloNegative said:

you're welcome :)^_^ just one question though, why use firebird? :o 

:) legacy system, and coz Firebird is known for its speed :) - I looked at Firebird, my impression is that its very much a closed eco system and just to set it up was a hassle. :(

 

Skysnake

Why is the snake in the sky?

Link to post
Share on other sites

How often is that client contact database update? 

Select the relevant info, client name, telephone with a simple query.  Place the data inside an AutoIt array, or local SQLite database. Clean it up (regex replace so that 812-123-1234 becomes 8121231234. Then modify the AutoIt script to cleanup your own input so that it is in the same format.  Search on your local copy of the data, then visually match the local results to the Firebird system. Yes? :)

 

Skysnake

Why is the snake in the sky?

Link to post
Share on other sites

hi,

searching on the local copy can be a way to go, good idea, thanks!

When someone calls, i have his number and then I want to find a callers name from the customer database of a crm system. Like a call monitor.

The CRM Software only allows read access to the firebird DB by odbc.

As the data is already there and can not be easily changed, i try my best to get useful results...

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...