I do have the output when I do a select
select * from testing
123varc
123@etets
[email protected]
470rt!dd
45sd3
tt123rc
[email protected]
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
123
123
123
470
453
123
123
470
453
123
Thanks a lot SKP
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
