After importing several rows to a table, I need to fire a SQL statement
to update 2 columns from a single column. My full name column needs to
be broken out to Fname and Lname columns. I have this query that works
well in Access but I need the SQL equivelent for it. Help apprecaited.
Thanks.
UPDATE tbl
SET Fname = Left([FullName], InStr(1,[FullName]," ")-1),
Lname = Right(Trim([FullName]),Len(Trim([FullName])) -
InStr(1,[FullName]," "))
--------------------
Result:
'InStr' is not a recognized function name.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You can do this in TSQL

say your value is

John Doe

you want it broken up

you can adapt this to your SQL Server column

declare @fname varchar(50)
declare @full varchar(100)
declare @lname varchar(50)
set @full = 'John Doe'

set @fname = rtrim(Left(@full, charindex(' ',@full)))
set @lname = substring(@full,charindex(' ',@full)+1,len(@full) - charindex('
',@full))
print @fname
print @lname



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Frank Py" <fpy (AT) proactnet (DOT) com> wrote

This is great, but how would I work that into an update statement like
the one I supplied the SQL Task? Could you give me an example? Sorry to
be a dumb ass, but help appreciated.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The whole point is you don't need to do the update if I read your situation
correctly.

You have a table in SQL Server that loks like

CREATE TABLE A(............................., FullName
varchar(200).............)

Typical value of FullName = 'Hilary Cotter'

You have a destination table of

CREATE TABLE B(............,FirstName varchar(75), Surname
varchar(75)...............)

You want :

FirstName = 'Hilary'
Surname = 'Cotter'


In the SourceSQLStatement of the datapump task instead of entering a table
name you have a SELECT statement.

SELECT
............,
rtrim(Left(fullname, charindex(' ',fullname))) as FirstName,
substring(fullname,charindex(' ',fullname)+1,len(fullname) - charindex('
',fullname)) as Surname,
....
FROM
A

This example is if your Source is SQl Server. Oracle has a similar way of
doing it, Access as you know does. If it is a text file then you can do it
inside an Active X Transform rather than an update.






--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Frank Py" <fpy (AT) proactnet (DOT) com> wrote