10862

How to replace all nonnumeric values?

<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

Recommend

  • filtering DocumentDb collection from Data Factory using unix-timestamp
  • Manipulating a dataframe within a Spark UDF
  • Extend Name Box
  • Select from table that does not exist
  • iOS - Is this a task for enums?
  • How can I emulate a recursive type definition in C++?
  • mapping between two ontologies
  • How to turn (A, B, C) into (AB, AC, BC) with Pig?
  • How to open html table in xls on click of a button
  • C# Excel interop - how to test if interop object is still working and performing a task?
  • Need code translation from VB to C#
  • how to get username into sql trigger when multiple users signed on from asp membership
  • Best practice to eliminate magic numbers within a member function
  • Why does java tzupdater add leap seconds?
  • During installation of Django, why do I keep getting ImportError: No module named django?
  • pillow imaging ImportError
  • Eliminate partial duplicate rows from result set
  • How do I configure context broker accept post requests from my remote sensor?
  • WPF ICommand CanExecute(): RaiseCanExecuteChanged() or automatic handling via DispatchTimer?
  • How solve “Qt: Untested Windows version 10.0 detected!”
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • Custom validator control occupying space even though display set to dynamic
  • The plugin 'org.apache.maven.plugins:maven-jboss-as-plugin' does not exist or no valid ver
  • All Classes Conforming to Protocol Inherit Default Implementation
  • Javascript Callbacks with Object constructor
  • Can I make an Android app that runs a web view in Chrome 39?
  • Trying to switch camera back to front but getting exception
  • How can I use Kendo UI with Razor?
  • Updated Ionic CLI but shows previous version (Windows)
  • ActionScript 2 vs ActionScript 3 performance
  • Websockets service method fails during R startup
  • How can I estimate amount of memory left with calling System.gc()?
  • Jquery - Jquery Wysiwyg return html as a string
  • Apache 2.4 - remove | delete | uninstall
  • Arrays break string types in Julia
  • How to delete a row from a dynamic generate table using jquery?
  • WPF Applying a trigger on binding failure
  • -fvisibility=hidden not passed by compiler for Debug builds
  • Java static initializers and reflection
  • Net Present Value in Excel for Grouped Recurring CF