10862
<strong>This is TERADATA</strong> (not SQL Server, not Oracle )
I have a column of phone numbers:
(312)9879878
(298)989-9878
430-394-2934
394s9048ds987
..........
I need to clean this column into
3129879878
2989899878
4303942934
3949048987
..........
So that only numbers should stay. All other letters, special characters, hyphens ... should be removed. How can I do this?
Answer1:
Which release of TD is running at your site? If it's 14 or you got the oTranslate UDF installed you can simply do an old trick nesting Translate:
oTranslate(phonenum, oTranslate(phonenum, '0123456789', ''), '')
Answer2:
<strong>Answer :</strong>
DECLARE @Input varchar(1000)
SET @Input = '01 vishal 98-)6543'
DECLARE @pos INT
SET @Pos = PATINDEX('%[^0-9]%',@Input)
WHILE @Pos > 0
BEGIN
SET @Input = STUFF(@Input,@pos,1,'')
SET @Pos = PATINDEX('%[^0-9]%',@Input)
END
SELECT @Input
<strong>Thank You, Vishal Patel</strong>
Answer3:
I have this function to pull numerics (0-9) from a string:
CREATE FUNCTION NumbersOnly(@STR VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @N INT
DECLARE @NN VARCHAR(1000)
SET @N = 0
SET @NN = ''
WHILE @N <= LEN(@STR)
BEGIN
IF SUBSTRING(@STR,@N,1) >= '0'
AND SUBSTRING(@STR,@N,1) <= '9'
BEGIN
SET @NN = @NN + SUBSTRING(@STR,@N,1)
END
SET @N = @N + 1
END
RETURN @NN
END
