QUERY - Ignore letters

[Canman2005]

Hi all

 

I have a db table which has a bunch of codes, they look like

 

D02434

EF02123

N4576

 

and so on.

 

How can I run a query to return just the numbers and ignore any letters

 

Can anyone help?

 

Thanks

 

Ed

[suttercain]

not efficient, bit would work

 

<?php

$result = "132HS3G";
$numbers = array('1', '2', '3', '4');
$filter = array('', '', '', '');
$cleaned = str_ireplace($numbers, $filter, $result);

echo $cleaned;

?>

 

The code above would echo HSG

 

You would need to create a numeric value for each number in the numbers and a corresponding blank value in the $filter.

[suttercain]

just thought of something... since you're filtering out numbers you could use str_replace since the one above only works on PHP 5 and above.... my bad. Peace out!

[Canman2005]

Is there now way of doing a

 

SELECT * FROM `table` ORDER BY `code` DESC

 

But ignore letters in the code field.

 

Basically I have tons of codes with different letters at the start of each code, I want to get a print out of all the codes, highest to lowest, despite what the letters are

[teng84]

ok try this SQL has a string function like php so you can manipulate the string before ouput that compltely

 

somethinglike

 

SELECT putstringfunctionhere(the field ) as key FROM `table` ORDER BY `key` DESC

 

try to search on mysql the string function and sure you can do this

[Canman2005]

what should I replace

 

putstringfunctionhere

 

with?

[teng84]

i cant remember that string manipulation in sql because i do that in my school days bu you have to replace the character and put leave only the letter it actually work just the same in php

 

but you cant get it odered by but having the php array_sort() it will sort your array by once no need for looping

 

putstringfunctionhere
[code]

what i mean by that is for you to look an appropriate sql string function  

[/code]

[Canman2005]

does anyone have any clues? Im a little baffled by what to do

[clearstatcache]

try ds ,

 

SELECT  cast(reverse(abs(reverse(Code_Field))) as unsigned)  as Code as key FROM `table` ORDER BY Code DESC

 

canman2005, i am hoping dat all ur codes are like ds in format

    D02434

    EF02123

    N4576

i mean letters first followed by numbers

no

  02434D

  2124FE45

coz ds will work only with ds format...

hope ds will help

[clearstatcache]

haven't tested its effieciency yet.....don't know if ds method is effiecient on ur part...just try...