Best match query Post: 302981719

Sponsored Content

Top Forums Programming Best match query Post 302981719 by stomp on Sunday 18th of September 2016 10:36:04 AM

09-18-2016

Registered User

The solution as a mysql function.

GOOD Very fast execution
BAD Debugging is far more difficult
BAD Programming Environment is inferior and limited compared to every other Programming language(including bash)
CAUTION Be sure to have your backups included user defined functions

Code:

DELIMITER $$

DROP FUNCTION IF EXISTS get_prefix;
DROP FUNCTION IF EXISTS get_rate;

/*

Usage from the os shell:

 echo "select get_prefix('$YOUR_NUMBER'),get_rate(get_prefix('$YOUR_NUMBER'));"  \
      | mysql -N -uMYSQL_USER -pMYSQL_PASSWORD DATABASE_NAME 

*/

CREATE FUNCTION get_prefix(wanted TEXT)
  RETURNS TEXT
BEGIN
  DECLARE current_length   INT;
  DECLARE prefix           TEXT;
  SET current_length       = LENGTH(wanted);

  count_loop: LOOP

    SET prefix = (
    SELECT destination
    FROM test 
    WHERE destination = SUBSTRING(wanted,1,current_length) 
    LIMIT 1);

    IF LENGTH(prefix) > 0 THEN LEAVE count_loop; END IF;
    SET current_length = current_length - 1;

    IF current_length = 0 THEN LEAVE count_loop; END IF;
  END LOOP;
  RETURN prefix;
    
END;

CREATE FUNCTION get_rate(prefix TEXT)
  RETURNS TEXT
BEGIN
  RETURN (SELECT rate FROM test WHERE destination = prefix LIMIT 1);
END;
$$
DELIMITER ;

The solution without a mysql function in bash:

GOOD Easy to write, Easy to Debug
GOOD Full Featured Bash Programming Environent
BAD A little slower than the mysql solution(~50-100ms here)

Code:

#!/bin/bash

# Usage: thisprog.sh <YOURPHONENUMBER>

target=$1 

function mysql_exec { 
    mysql -uroot -pblub123 --skip-column-names test "${@}"; }

for((c=${#target};c>0;c--)) ; do
    prefix=$(mysql_exec -e \
        "SELECT destination FROM test WHERE destination = '${target:0:$c}' LIMIT 1")
    [ -n "$prefix" ] && break
done

rate=$(mysql_exec -e "SELECT rate FROM test WHERE destination = '$prefix' LIMIT 1")
echo "$target $prefix $rate"

Last edited by stomp; 10-03-2016 at 09:29 AM.. Reason: Added Advantages/Disadvantages to every script

stomp

View Public Profile for stomp

Find all posts by stomp

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

add the output of a query to a variable to be used in another query

I would like to use the result of a query in another query. How do I redirect/add the output to another variable? $result = odbc_exec($connect, $query); while ($row = odbc_fetch_array($result)) { echo $row,"\n"; } odbc_close($connect); ?> This will output hostnames: host1...

2. UNIX and Linux Applications

mysql query all entries which 'dont' match

I am trying to query a list of hosts and extract all entries which 'dont' match. SELECT LOGS.host, GOODLIST.host FROM LOGS,db.GOODLIST WHERE (LOGS.host <> GOODLIST.host)When I use this query, it is very very slow. Matching the host with the GOODLIST.host works great and fast but when I use <>...

3. Shell Programming and Scripting

Query Oracle tables and return values to shell script that calls the query

Hi, I have a requirement as below which needs to be done viz UNIX shell script (1) I have to connect to an Oracle database (2) Exexute "SELECT field_status from table 1" query on one of the tables. (3) Based on the result that I get from point (2), I have to update another table in the...

4. Programming

sql query to match condition from other table (time sensitive)

I know little SQL and could really use a hand here. I need to get the fields last_name, first_name and email from the table users and only if the entery with the same pkey in the table addresses, in the city column is Seattle. So if the city in the addresses table is Seattle, go to the table...

5. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ...

6. Shell Programming and Scripting

Match pattern1 in file, match pattern2, substitute value1 in line

not getting anywhere with this an xml file contains multiple clients set up with same tags, different values. I need to parse the file for client foo, and change the value of tag "64bit" from false to true. cat clients.xml <Client type"FIX"> <ClientName>foo</ClientName>...

7. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Hello All, I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns. So my requirement is to generate a output query which will have 20 columns all the...

8. Shell Programming and Scripting

awk to update file based on partial match in field1 and exact match in field2

I am trying to create a cronjob that will run on startup that will look at a list.txt file to see if there is a later version of a database using database.txt as the source. The matching lines are written to output. $1 in database.txt will be in list.txt as a partial match. $2 of database.txt...

9. Shell Programming and Scripting

awk to print match or non-match and select fields/patterns for non-matches

In the awk below I am trying to output those lines that Match between file1 and file2, those Missing in file1, and those missing in file2. Using each $1,$2,$4,$5 value as a key to match on, that is if those 4 fields are found in both files the match, but if those 4 fields are not found then missing...

10. UNIX for Beginners Questions & Answers

Data match 2 files based on first 2 columns matching only and join if match

Hi, i have 2 files , the data i need to match is in masterfile and i need to pull out column 3 from master if column 1 and 2 match and output entire row to new file I have tried with join and awk and i keep getting blank outputs or same file is there an easier way than what i am...

LEARN ABOUT CENTOS

set_color

set_color(1)							       fish							      set_color(1)

NAME

       set_color - set_color - set the terminal color

set_color - set the terminal color
   Synopsis
       set_color [-v --version] [-h --help] [-b --background COLOR] [COLOR]

   Description
       Change the foreground and/or background color of the terminal. COLOR is one of black, red, green, brown, yellow, blue, magenta, purple,
       cyan, white and normal.

       o -b, --background Set the background color

       o -c, --print-colors Prints a list of all valid color names

       o -h, --help Display help message and exit

       o -o, --bold Set bold or extra bright mode

       o -u, --underline Set underlined mode

       o -v, --version Display version and exit

       Calling set_color normal will set the terminal color to whatever is the default color of the terminal.

       Some terminals use the --bold escape sequence to switch to a brighter color set. On such terminals, set_color white will result in a grey
       font color, while set_color --bold white will result in a white font color.

       Not all terminal emulators support all these features. This is not a bug in set_color but a missing feature in the terminal emulator.

       set_color uses the terminfo database to look up how to change terminal colors on whatever terminal is in use. Some systems have old and
       incomplete terminfo databases, and may lack color information for terminals that support it. Download and install the latest version of
       ncurses and recompile fish against it in order to fix this issue.

Version 1.23.1							  Sun Jan 8 2012						      set_color(1)