fn:replace

The fn:replace function replaces parts of a string that match a regular expression. The regular expression syntax used is defined by XML Schema with a few modifications/additions in XQueryXPath/XSLT. The $pattern argument is a regular expression. While it is nice to have the power of regular expressions, if you simply want to replace a particular sequence of characters you don't have to be familiar with regular expressions to do that; you can just specify the string you want replaced for $pattern, as long as it doesn't contain any special characters.

The $replacement argument specifies a string (not a pattern) that is to be used as a replacement.

The $flags argument allows for additional options in the interpretation of the regular expression, such as multi-line processing and case insensitivity. Flags, reluctant quantifiers and sub-expressions are features that are covered.

For more examples of XQueryXPath/XSLT/XML Schema regular expressions, 

This description is © Copyright 2007, O'Reilly Media. It is excerpted from the book XQuery by Priscilla Walmsley, O'Reilly, 2007. For a complete explanation of this function, please refer to Appendix A of the book.

Arguments and Return Type

Name	Type	Description
$input	xs:string?	the string to change
$pattern	xs:string	regular expression to match the areas to be replaced
$replacement	xs:string	the replacement string
$flags	xs:string	flags that control multiline mode, case insensitivity, etc.
return value	xs:string

Examples

XQuery Example	Results
replace('query', 'r', 'as')	queasy
replace('query', 'qu', 'quack')	quackery
replace('query', '[ry]', 'l')	quell
replace('query', '[ry]+', 'l')	quel
replace('query', 'z', 'a')	query
replace('query', 'query', '')	zero-length string
replace( (), 'r', 'as')	zero-length string
replace('query', 'r?', 'as')	Error FORX0003
replace('query', '(r', 'as')	Error FORX0002
replace('Chapter', '(Chap)|(Chapter)', 'x')	xter
The following examples show the difference between reluctant and regular quantifiers:
replace('reluctant', 'r.*t', 'X')	X
replace('reluctant', 'r.*?t', 'X')	Xant
replace('aaah', 'a{2,3}', 'X')	Xh
replace('aaah', 'a{2,3}?', 'X')	Xah
replace('aaaah', 'a{2,3}', 'X')	Xah
replace('aaaah', 'a{2,3}?', 'X')	XXh
The following examples exhibit the use of sub-expressions:
replace('Chap 2...Chap 3...Chap 4...', 'Chap (\d)', 'Sec $1.0')	Sec 2.0...Sec 3.0...Sec 4.0...
replace('abc123', '([a-z])', '$1x')	axbxcx123
replace('2315551212', '(\d{3})(\d{3})(\d{4})', '($1) $2-$3')	(231) 555-1212
replace('2006-10-18', '\d{2}(\d{2})-(\d{2})-(\d{2})', '$2/$3/$1')	10/18/06
replace('25', '(\d+)', '\$$1.00')	$25.00

How to Import from Excel to Oracle with SQL Developer

Step 0: The Empty Oracle Table and your Excel File

You have an Oracle table and you have one or more Excel files.

Data here but not there!?!

You do know how to view multiple objects at once in SQL Developer, right?

Step 1: Mouse-right click – Import Data

Yes, it's that easy.

Step 2: Select your input (XLS) file

Yes, we also support XLSX, CSV, etc

Step 3: Verify the data being read

Mind the headers!

Does your Excel file have column headers? Do we want to treat those as a row to the table? Probably not. The default options take care of this. You can also choose to preview more than 100 rows.

Here's what it looks like if you uncheck the 'Header' box

Sometimes you may want the column headers as a row in the table?

Sometimes your Excel file has multiple headers, or you may need to only import a certain subset of the spreadsheet. Use the 'Skip Rows' option to get the right data.

Step 4: Create a script or import automatically

Script or do it for me?

For this exercise the 'Insert' method will be used.

Step 5:

Choose the Excel columns to be imported

You may have an Excel file with 100 columns but your table only has 30. This is where you tell SQL Developer what columns are to be used for the import. You can also change up the column order, which may make the next step a bit easier.

Step 6:

If you're not paying attention and just letting the wizard guide you home, then now is the time to wake up. There's a good chance the column order of the Excel file won't match the definition of your table. This is where you will tell SQL Developer what columns in the spreadsheet match up to what columns in the Oracle table.

Step 7: Verify your settings

Hit the 'verify' button. Fix any mistakes.

Ruh roh raggy!

SQL Developer is telling you it doesn't know how to reconcile the data for this DATE column. We need to know what the DATE FORMAT is.

So we need to go back to the Column definition wizard and inspect the HIRE_DATE column settings.

You need to look at how the dates are stored in the spreadsheet and write them in terms that Oracle can understand. This will be used on the INSERTs via a TO_DATE() function that will turn your Excel string into an actual DATE value.

After correcting this, go back to the Verification screen and see if that fixes the problem.

Step 8:

Everything looks right!

Click on the 'Finish' button.

Step 9: Verify the import look at your new table data

The data is there and the dates look right!

Note the 'Log' panel. SQL Developer is processing the records in batches of 50. No errors and the data is there!