convoluted extraction of data

Dev Shed Forums Sponsor:

November 24th, 2003, 11:37 AM

tina_a

Registered User

Join Date: Oct 2003

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

convoluted extraction of data

Hello,

I have this weird extraction of data from a column for which I am not sure how to put the query.

Example:
Let us say the table Table1 has column "ParcelNo" with values shown.
Table1: ParcelNo
------------
21-912-60-03
11-912-60-03
129-16-0-033

If a user enters to search for parcel No: "2-91-6-0". I need to strip the dashes and the search variable becomes "29160".

The return from a SELECT on the ParcelNo field with value "29160" should return values:
(21-912-60-03 and 129-16-0-033) and will not return (11-912-60-03).

The way it extracts is in the order of the number. It will not extract the second record value because there is no 2 before 9.
Req: It should check that the Parcel No has all the numbers from the "21960" plus it should be arranged in the same order. However any other number/dash could be in between.

Please help me find a solution to this?

thanks
Tina

November 24th, 2003, 12:22 PM

Ucht

This is only a test

Join Date: Mar 2003

Location: Off the air

Posts: 2,893

Time spent in forums: 1 Month 5 Days 20 h 1 m 43 sec
Reputation Power: 147

You should be able to just wildcard your search. I'm not sure what languages you're working with but in PHP/MySQL, I'd do something like this:

PHP Code:


		
			
//original search string

$find = "2-91-6-0";



//strip dashes from string

$find_stripped = str_replace("-","",$find);





//convert into string with wildcards ('%')

$find_final = "%";



for($i=0;$i < strlen($find_stripped);$i++)

{

  $find_final .= substr($find_stripped,$i,1) . "%";

}



//create query with final wildcard variable

$sql = "SELECT * FROM 

         Table1

        WHERE

         ParcelNo LIKE '$find_final'";

This will give you the query:

SELECT * FROM
Table1
WHERE
ParcelNo LIKE '%2%9%1%6%0%'

which should work for you.

__________________
"Not to offend our Swedish listeners ... if we have any, that is—"
"—But your team's rubbish."
(Sun webcast, Sweden vs. Paraguay)

Who needs corporate radio?
WeFunkRadio.com | Global Pop Conspiracy | Radio Paradise | SomaFM | The Classic Soul Network | Boot Liquor | WFMU Freeform Radio

Viewing: Dev Shed Forums > Databases > Database Management > convoluted extraction of data

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump