Skip to Main Content
  • Questions

  • How can I concatenate fields/values using SQL *Loader?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, andy.

Asked: March 13, 2001 - 10:14 am UTC

Last updated: February 29, 2008 - 1:55 am UTC

Version: 8.0.5

Viewed 10K+ times! This question is

You Asked

Example

My data file NameAddress.dat is in the following format:
'john msmith 125 any road....'
'mary xpoppins Super Highway...'

first name is characters 1-10, middle initial is character 11 and second name is characters 12-21

Importing these to seperate colums is ok as in the control file below:


LOAD DATA

INFILE 'NameAddress.dat'
BADFILE 'NameAddress.bad'
DISCARDFILE 'NameAddress.dsc'

APPEND

INTO TABLE S_ACCOUNT_IF
(FIRST_NAME POSITION(001:010) CHAR,
INITIAL POSITION(011:011) CHAR,
LAST_NAME POSITION(012:021) CHAR, .....


However I have a column (FIRST_AND_LAST_NAME) which I would like to populate with characters 1-10 concatenated to characters 12-21.

Is this possible from within SQL *Loader?
I have tried with ||, CONCAT etc... but can not find any syntax that will work.

and Tom said...

INTO TABLE S_ACCOUNT_IF
(FIRST_NAME POSITION(001:010) CHAR,
INITIAL POSITION(011:011) CHAR,
LAST_NAME POSITION(012:021) CHAR,
FIRST_AND_LAST_NAME position(1:1) ":first_name || ' ' || :last_name",

will do it. You'll just use a SQL function to access the bind values :first_name and :last_name. Note that first_and_last_name can have any old position you want - you won't be using that bind column.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Concatenating fields with SQL Loader

Steve, February 28, 2008 - 5:26 pm UTC

The proposed solution works only if you have columns in the table named FIRST_NAME and LAST_NAME.

What if those columns do not exist and you wanted to load only FIRST_AND_LAST_NAME ?
Tom Kyte
February 29, 2008 - 1:55 am UTC 

ops$tkyte%ORA10GR2> select * from t;

no rows selected

ops$tkyte%ORA10GR2> !cat t.ctl
load data
infile *
replace
into table t
(
   FIRST_AND_LAST_NAME position(1:21) "rtrim(substr(:first_and_last_name,1,10))||' '||rtrim(substr(:first_and_last_name,12,10))"
)
begindata
Thomas     Kyte

ops$tkyte%ORA10GR2> !sqlldr / t.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Feb 29 01:54:22 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 1

ops$tkyte%ORA10GR2> select * from t;

FIRST_AND_LAST_NAME
--------------------------------------------------
Thomas Kyte

Stanislav Osmera, May 11, 2010 - 5:44 am UTC

what if the columns does not exists and the file is delimited file (not position specified file)
like:

begindata
"Thomas","Kyte"
"John","Smith"

I can't use filler and I did not find any other solution other then loading into temporary table and then process into final table.

Thank you.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.