removing alphabetic characters from result in mysql select statement

Question

I do have the output when I do a select

select * from testing

 123varc    
 123@etets
 123@ppp.c
 470rt!dd
 45sd3
 tt123rc
 123@ppp.c
 470rt!dd
 45sd3
 tt123rc

How do I write the query in mysql so that it will remove all the alphabetic characters and show me the result.

I mean to say the result will come as

Thanks a lot SKP

Is this query for extract data to a new field or for real time searches? Cleaning your data in real time using SQL is very seldom a good idea. — Joachim Isaksson, Mar 21 '16 at 14:10
http://stackoverflow.com/a/11432734/477878 seems to have an answer for your question though (even if the original question was the reverse problem) — Joachim Isaksson, Mar 21 '16 at 14:12

spencer7593 · Answer 1 · 2016-03-21T14:46:13.373

There is no single MySQL built-in function that will accomplish that string manipulation.

It looks like you want to remove more than "alphabetic" characters; there's a @ character also being removed. It raises the question what you want to do with other characters that could appear in a string, like spaces, tabs, semicolons, periods, and a bunchload of other characters. We can't tell from the question.

But it looks almost as if what you really want is to "keep" just numeric digit characters, 0 thru 9.

To keep the SQL simple (to hide the complexity) we could write a MySQL function (stored program) that returns a string. And then reference that function in the SQL.

There's a couple of downsides to that. Another database object, the SQL that uses the function isn't portable to another database (without also implementing the function).

But the native SQL to do this isn't pretty, no matter which way you slice it.

If the maximum number of characters in the string is limited, as in ten characters (the longest string in your example data). I'd chop the string up into ten individual characters, and then see if each character is one we want to return.

As a first step, chopping up the string into individual characters and concatenating them back together:

 SELECT CONCAT( SUBSTR(foo,1,1) 
              , SUBSTR(foo,2,1) 
              , SUBSTR(foo,3,1) 
              , ...
              , SUBSTR(foo,10,1)
       ) AS foo

(That doesn't do any removal of characters, that's just illustrating a pattern. Expanding that pattern, adding in a check of each individual character, returning either the character (if it's one we want to keep), or the empty string (for the characters we don't want to keep.)

SELECT CONCAT( IF( INSTR('1234567890',SUBSTR(foo,1,1)), SUBSTR(foo,1,1), '')
             , IF( INSTR('1234567890',SUBSTR(foo,2,1)), SUBSTR(foo,2,1), '')
             , IF( INSTR('1234567890',SUBSTR(foo,3,1)), SUBSTR(foo,3,1), '')
             , ...
             , IF( INSTR('1234567890',SUBSTR(foo,10,1)), SUBSTR(foo,10,1), '')
       ) AS bar

Of course, if the goal is to identify the characters to be removed, and keep everything else, you could swap that around... return the empty string if the character matches, otherwise return the character.

SELECT CONCAT( IF( INSTR('@#$abc',SUBSTR(foo,1,1)), '', SUBSTR(foo,1,1))
             , IF( INSTR('@#$abc',SUBSTR(foo,2,1)), '', SUBSTR(foo,2,1))
             , IF( INSTR('@#$abc',SUBSTR(foo,3,1)), '', SUBSTR(foo,3,1))
             , ...
             , IF( INSTR('@#$abc',SUBSTR(foo,10,1)), '', SUBSTR(foo,10,1)) 
       ) AS rab

removing alphabetic characters from result in mysql select statement

1 Answers1