Please - help with sql query needed

[Allow2010]

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 January 21, 2018 by Allow2010

[Deye]

Allow2010,

See if this thread helps you out:
https://www.autoitscript.com/forum/topic/189734-how-can-i-make-autoit-copy-and-paste-only-numbers/

[Allow2010]

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 :-)

[XaelloNegative]

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. )

[Allow2010]

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 January 22, 2018 by Allow2010

[XaelloNegative]

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')

 

[Allow2010]

wow, this look interresting, will take some time to play with it and to understand...thank you .-)

 

[XaelloNegative]

you're welcome  just one question though, why use firebird?  

[jchd]

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.

[Skysnake]

14 hours ago, XaelloNegative said:

you're welcome  just one question though, why use firebird?  

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]

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?

 

[Allow2010]

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...