Get Numeric Values only using SQL ?

I have table with one column. type varchar..
PHONE
---------------
99samn44423m44
8034skdf3453a2
909090abc4567d
i want to get numeric values.. i mean my output will be
PHONE
---------------
994442344
80343453a2
9090904567
thanks in advance..
Sateesh

Questions by sateesh44   answers by sateesh44

Showing Answers 1 - 6 of 6 Answers

Varsha

  • Dec 8th, 2011
 

This can be done by using the TRANSLATE function as it replaces character by character.

Swathy

  • Dec 12th, 2011
 

Varsha could you please write the query for this above question?

  Was this answer useful?  Yes

kirathakudu

  • Dec 13th, 2011
 

translate(PHONE,[a,b,c,d,e,f..etc],SPACE)

where ever there is an alphabet it will be replaced by space

eg:

PHONE = 32663cdefh3456
after translate PHONE = 32663 3456

translate( string1, string_to_replace, replacement_string )

  Was this answer useful?  Yes

sanjay

  • Jan 5th, 2012
 

Code
  1. SELECT REPLACE(TRANSLATE(909090abc4567d,a,b,c,d,e,f,g, ), ,) FROM dual

sriram

  • Feb 24th, 2012
 

Code
  1. SELECT columnname FROM tablename WHERE regexp_like(columnname,^[[:digit:]]+$) ;

  Was this answer useful?  Yes

naga

  • Mar 12th, 2012
 

Code
  1. SELECT REPLACE(TRANSLATE(LOWER(99sAmn44423mz44),abcdefghijklmnopqrstuvwxyz, ), ,) FROM dual

Abhishek

  • Aug 23rd, 2012
 

Code
  1. declare  @temp_numeric TABLE (value nvarchar(500))

  2. declare @i int=1,@len int,@ans nvarchar(100)=,@str nvarchar(100)

  3. DECLARE @count INT

  4.  

  5. SELECT VALUE INTO #temp FROM dbo.numeric

  6.  

  7. SELECT * FROM #temp

  8.  

  9. SET @count=(SELECT COUNT(*) FROM dbo.#temp)

  10. WHILE(@count>0)

  11. BEGIN

  12.  

  13.  

  14. SET @str = (SELECT TOP (1) value FROM #temp)

  15.  

  16. SET @len=(SELECT LEN(@str))

  17.  

  18. while(@i<=@len)

  19.  BEGIN

  20.      

  21.   IF(isnumeric(SUBSTRING(@str,@i,1))=1)

  22.   begin

  23.   SET @ans=@ans+(SELECT SUBSTRING(@str,@i,1))

  24.   end

  25.   SET @i=@i+1

  26.  

  27.  END --inner loop ends

  28.  

  29. DELETE TOP(1) FROM #temp

  30. SET @count=(SELECT COUNT(*) FROM #temp)

  31.  

  32.  INSERT INTO @temp_numeric VALUES(@ans)

  33.  

  34.  SET @i=1

  35.  SET @ans=

  36. END --outer while loop

  37.  

  38.  

  39.  SELECT * FROM @temp_numeric

  40.  

  41.  DROP TABLE #temp


  Was this answer useful?  Yes

stalinK

  • Jul 23rd, 2014
 

Code
  1. DECLARE @Temp VARCHAR(100)=(STA123LIN)

  2. Declare @NumRange AS varchar(50) = %[0-9]%

  3.     While PatIndex(@NumRange, @Temp) > 0

  4.         SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, )

  5.  

  6. SELECT @Temp

stalink

  • Sep 22nd, 2014
 

DECLARE @Temp VARCHAR(100)=(StalinKIN095815)
Declare @NumRange as varchar(50) = %[0-9]%
While PatIndex(@NumRange, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, )

SELECT @Temp

  Was this answer useful?  Yes

rajesh anasuri

  • Sep 3rd, 2015
 

SELECT REGEXP_REPLACE(AB12CD34EF,[A-Z], ) FROM DUAL ;

  Was this answer useful?  Yes

Krishna

  • Mar 30th, 2016
 

Code
  1. SELECT REGEXP_REPLACE(<Column Name>,[A-Za-z]) FROM <Table Name>;

  Was this answer useful?  Yes

Ganezh

  • Apr 20th, 2016
 

Code
  1. SELECT REGEXP_REPLACE(AB12CD34EF,A|B|C|D|E|F, ) FROM DUAL;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions