Oracle: Converting a string to a number

For a customer, I was converting an Access database to an Oracle relational structure. As it turned out, several of the Access number fields were strings and the users had entered number with errors (example: 12.900.000) and text (example: UNLIMITED). So I put together a Sql statement to display non-numbers so the users could correct the corrupt data. I didn't want to make any assumptions. So I came up with the following:

 SELECT tbl.columnValidating
 FROM tableName tbl
 WHERE TRANSLATE(tbl.columnValidating,'~,$0123456789','~') IS NOT NULL
   AND tbl.columnValidating <> 'UNLIMITED'
   AND tbl.columnValidating <> 'any other text you want to filter out'

Comments

Post a Comment

Popular posts from this blog

Debug VBScript / VBS files in Visual Studio 2010

Image
I needed to be able to step through a VBScript file that was being run in an environment that I did not have on my machine and this environment did not have a debug environment that I could use to step through the VBScript being executed.  The VBScript was simply looking to see if a folder exists on a network drive and if the folder did exist, create the folder.   Then the script would copy some files to the network drive / folder location.  My machine and user id had access to this network drive so I could run this VBScript from my machine. So placed the VBScript file (script.vbs) on my computer at C:\Projects\VBS\. Then I ran the Visual Studio 2010 Command Prompt as Admin and changed the directory to the above location: I then typed in the following and selected the enter key to run the Visual Studio Just-In-Time Debugger: cscript /x /d script.vbs The following will be presented: Be sure you select the "New instance of Microsoft Visual Studio 2010" and selec
Read more

A good use of Common Table Expressions

Image
The problem I had a situation recently were I needed to find a set of data that was structured logically as a tree and was stored as a single table.   I had an initial id and I needed to search for children of this id.   Then I needed to use the children’s ids and search for their children.   This continued until there were no more children of an id.   This structure could have a plethora of different sets of children. For examples the boxes below represent an id and I only show three levels deep but the level depth was not restricted: Once I found all of an id’s children, I needed to do some manipulation of this data.   To make this problem easier to understand and use a ubiquitous example, the ids will be folders and there are files (stored in another table) associated with the folders.   Therefore for this problem, I need to find all the child folders for a specific folder, the files associated with the initial folder and the files associated with all the found child folder
Read more

Setting Up Visual Studio Environment for Selenium

Image
Preconditions Visual Studio (VS) has been installed. A successful installation of VS 2017 needs to contain the following workloads: Universal Windows Platform development .Net desktop development ASP.Net and web development .Net Core cross-platform development Ensure the following folder structure exists on you main OS drive.  In INDOT's case this is the C: driver. Selenium_Testing\Projects Our current standard is to then provide the project or sub-project name, then "Branches", then the current release and the normal VS solution structure.  So in the case of Lab Buddy the structure is the following: \Selenium_Testing\Projects\FieldAssistant_LabBuddy\Branches\Release4 Create Project Once a successful Visual Studio (VS) installation has occurred, you will want to create an new VS  1) Select File -> New -> then Project... 2)The "New Project" dialog window is displayed.  Setup a C# Console App (.Net Framework) and Name it something like &qu
Read more