1

I want remove letters from my string like this:

select CAST(dstring as number), * from myTable

dstring can be M-St2, DE2, M12,

result should be 2, 2, 12

Thanks

kiterstefan
  • 57
  • 1
  • 3
  • 2
    Possible duplicate of [How to get only Digits from String in mysql?](https://stackoverflow.com/questions/37268248/how-to-get-only-digits-from-string-in-mysql) – Nick Jul 19 '17 at 09:10
  • @NickSavenia nope, that's not a duplicate, since in that question all numbers are in the front and a single number is returned. – Shadow Jul 19 '17 at 09:29

3 Answers3

1

Replace the number in column. For example 'John12' delete '12' from column then return 'John', then replace 'John'. You can get '12'.

SELECT 
REPLACE(dstring, REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE (
    REPLACE( REPLACE( REPLACE( REPLACE(dstring ,'0','')
    ,'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''), '')
FROM myTable;
Kemal Güler
  • 608
  • 1
  • 6
  • 21
0

Nailed it.... this will remove a-z and A-Z leaving all other characters behind...

UPDATE some_table SET some_field = REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(some_field,'a','') ,'b','') ,'c','') ,'d','') ,'e','') ,'f','') ,'g','') ,'h','') ,'i','') ,'j','') ,'k','') ,'l','') ,'m','') ,'n','') ,'o','') ,'p','') ,'q','') ,'r','') ,'s','') ,'t','') ,'u','') ,'v','') ,'w','') ,'x','') ,'y','') ,'z','') ,'A','') ,'B','') ,'C','') ,'D','') ,'E','') ,'F','') ,'G','') ,'H','') ,'I','') ,'J','') ,'K','') ,'L','') ,'M','') ,'N','') ,'O','') ,'P','') ,'Q','') ,'R','') ,'S','') ,'T','') ,'U','') ,'V','') ,'W','') ,'X','') ,'Y','') ,'Z','');

And here's my PHP to generate that (add any other characters you need in the $to_remove string:

$to_remove = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
$removals = str_split($to_remove,1);
$stmt = "some_field";
foreach($removals as $x) {
    $stmt = " REPLACE(".$stmt.",'".$x."','') ";
}
$stmt = "update some_table set some_field = ".$stmt." ";
echo($stmt);
Scott
  • 695
  • 1
  • 8
  • 16
-3

Try to do this code,it may helpfull to you.

dstring can be M-St2, DE2, M12,

result should be 2, 2, 12

only display numeric value.

SELECT NumericOnly(dstring) as number from myTable

anu
  • 1
  • 1
  • 1
    I don't think mysql has such a function. Do you have a reference for it? – P.Salmon Jul 19 '17 at 09:21
  • actually you want to remove letters from the data..is it – anu Jul 19 '17 at 09:55
  • Yes , but how does your solution help then NumericOnly is not a mysql function (that I can find)? – P.Salmon Jul 19 '17 at 10:32
  • please refer this link – anu Jul 19 '17 at 10:38
  • https://venerableagents.wordpress.com/2011/01/29/mysql-numeric-functions/ – anu Jul 19 '17 at 10:38
  • pleasen refer this link,may be it will help https://venerableagents.wordpress.com/2011/01/29/mysql-numeric-functions/ – anu Jul 19 '17 at 10:39
  • This is not a standard mysql function, so, you should reference the website **and** the code (yes, you must copy the code) in your answer. Without these, your answer is meaningless. – Shadow Jul 19 '17 at 14:28