Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Splitting name field into 2 name fields not working 3

Status
Not open for further replies.
Wyldcard9

Wyldcard9

Programmer
Feb 5, 2004
82
US
DECLARE @v_pos_FirstNameStart INT
DECLARE @v_pos_LastNameStart INT

UPDATE [Pension Forms].[dbo].[XFRFUND10]
SET
@v_pos_LastNameStart= charindex(' ', MEMBERFULLNAME,1 ),
LASTNAME=left(MEMBERFULLNAME, @v_pos_LastNameStart),
@v_pos_FirstNameStart= charindex(' ', MEMBERFULLNAME,@v_pos_LastNameStart + 2 ),
FIRSTNAME = ltrim(right(MEMBERFULLNAME, LEN(MEMBERFULLNAME) - @v_pos_LastNameStart))


I get:
Server: Msg 536, Level 16, State 1, Line 4
Invalid length parameter passed to the substring function.
The statement has been terminated.

What am I doing wrong?
 
Sort by date Sort by votes
I don't think you can set a variable and reference the new value of the variable in the same statement. Try removing the variables entirely, just put the expressions you're assigning to them in place of the later references.
 
Upvote 0 Downvote
The SET statements in an Update clause are evaluated right to left. This is the opposite of SELECT clause. So the first assignment is
Code: 
FIRSTNAME = ltrim(right(MEMBERFULLNAME, LEN(MEMBERFULLNAME) - @v_pos_LastNameStart))
and since @v_pos_LastNameStart hasn't been assigned yet it is NULL. x - NULL is NULL.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
I think you are talking about this right:

DECLARE @v_pos_FirstNameStart INT
DECLARE @v_pos_LastNameStart INT

UPDATE [Pension Forms].[dbo].[XFRFUND10]
SET
FIRSTNAME = ltrim(right(MEMBERFULLNAME, LEN(MEMBERFULLNAME) - @v_pos_LastNameStart)),
LASTNAME=left(MEMBERFULLNAME, @v_pos_LastNameStart)

My MEMBERFULLNAME field has variable length names; some last names are longer than others. I can't hard code @v_pos_LastNameStart, and @v_pos_LastNameStart can I?

I'm sorry, I am not familiar with the nomenclature. This parsed, and ran, but did not update the fields. Ya'll are getting me closer, but I am not comprehending totally.
 
Upvote 0 Downvote
You left out the assignments statements to your variables. ESquared is rarely wrong, but today is an exception. :)
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
I realized after I posted it that it wasn't something I'd thoroughly investigated about SQL.

I made the assumption based on past programming experience.

It's interesting to know! Thanks for the information, Karl.
 
Upvote 0 Downvote
donutman,

Are you talking about like this:

DECLARE @v_pos_FirstNameStart INT
DECLARE @v_pos_LastNameStart INT

UPDATE [Pension Forms].[dbo].[XFRFUND10]
SET
@v_pos_FirstNameStart= charindex(' ', MEMBERFULLNAME,@v_pos_LastNameStart + 2 ),
@v_pos_LastNameStart= charindex(' ', MEMBERFULLNAME,1 ),
FIRSTNAME = ltrim(right(MEMBERFULLNAME, LEN(MEMBERFULLNAME) - @v_pos_LastNameStart)),
LASTNAME=left(MEMBERFULLNAME, @v_pos_LastNameStart)

That puts me to the original error. I tried the v_pos_FirstNameStart, and v_pos_LastNameStart define outside of the UPDATE, but the parser does not like that.
 
Upvote 0 Downvote
I will gladly give a star to everyone of your posts in this thread donutman when it starts working. I have spent all day on this(merging two databases with vastly different formats of data).
 
Upvote 0 Downvote
Try this out:
Code: 
DECLARE @v_pos_FirstNameStart INT
DECLARE @v_pos_LastNameStart INT

UPDATE [Pension Forms].[dbo].[XFRFUND10] 
   SET 
      FIRSTNAME = ltrim(right(MEMBERFULLNAME, LEN(MEMBERFULLNAME) - @v_pos_LastNameStart)),
      @v_pos_FirstNameStart= charindex(' ', MEMBERFULLNAME,@v_pos_LastNameStart + 2 ),
      LASTNAME=left(MEMBERFULLNAME, @v_pos_LastNameStart),
      @v_pos_LastNameStart= charindex(' ', MEMBERFULLNAME,1)
We can also check your data to see if that is causing it to fail...but obviously it has to get past the sytax.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
WyldCard9, oh just realized that you may have thought I was talking to you when I made the crack about the star. ESquared and I are friends.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Okay that parses great. Running gets this error.

Server: Msg 536, Level 16, State 1, Line 4
Invalid length parameter passed to the substring function.
The statement has been terminated.

*lol* I knew you were talking to ESquared, but I will give you a star for each post, once this gets working. I am beyond frustrated today.

Sample data from MEMBERFULLNAME:

'MACCULLEN YONG '
'BINUYA RAYMOND '
'YOSHIDA JOE '
'HIGGINBOTHAM EV '

I'm gonna have to pick this up tomorrow, gotta head out. I appreciate all the time you have been taking to help :)
 
Upvote 0 Downvote
Let's start from scratch:
Code: 
   UPDATE [Pension Forms].[dbo].[XFRFUND10] 
      SET LastName=Left(MEMBERFULLNAME, CharIndex(' ',MEMBERFULLNAME)-1),
         FirstName=RTrim(substring(MEMBERFULLNAME, CharIndex(' ',MEMBERFULLNAME)+1,50))
That should avoid the variable issue. You could also do this in two steps. When you're having trouble that's a good thing to do.
-Karl
 
Upvote 0 Downvote
Huh? Huh? What did I say? I refer our kind readers to the following: "Try removing the variables entirely, just put the expressions you're assigning to them in place of the later references."

:)
 
Upvote 0 Downvote
Don't get too excited! We both stated falsehoods! Check this out.
Code: 
DECLARE @T int
CREATE TABLE #Temp
   (Test int)
INSERT INTO #Temp SELECT 0
INSERT INTO #Temp SELECT 5
SET @T=1 
UPDATE #Temp
   SET Test=@T, @T=2, @T=@T+1, @T=8, @T=Test, @T=@T+1 
SELECT * FROM #Temp
Results:
1
6
ABSOLUTELY AMAZING
[ul]
[li] Processing starts with the 2nd assignment.[/li]
[li] Processing continues to the right.[/li]
[li] Last step is 1st assignment.[/li]
[/ul]
I never dreamed of testing more than 2 assignments when I learned the processing order. Better believe you shouldn't count on that for Yukon. Best to avoid the issue (as you naively suggested :)).
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
UPDATE [Pension Forms].[dbo].[XFRFUND10]
SET LastName=Left(MEMBERFULLNAME, CharIndex(' ',MEMBERFULLNAME)-1),
FirstName=RTrim(substring(MEMBERFULLNAME, CharIndex(' ',MEMBERFULLNAME)+1,15))


With a small modification to your code suggestion it worked :) I realized that firstname was only 15 characters long. Thank you both. I am going to leave stars. I really appreciate you helping me out.
 
Upvote 0 Downvote
Hey Karl,

I think it's not quite as you suggested.

Here's my take on it:

[ul][li]All variable assignments are made first, left to right.[/li]
[li]Variable references are dynamic and refer to the variable's current updated value as set in order by each assignment.[/li]
[li]All column assignments are made next.[/li]
[li]Column references in a variable or column assignment refer to the pre-update value.[/li]
[li]The syntax
[ul][tt]@Variable = Column = Expression[/tt][/ul]
can be treated as two assignments,
[ul][tt]@Variable = Expression, Column = Expression[/tt][/ul]
with evaluation made as already stated.[/li]
[/ul]

And it is very interesting.

Following the rules I just gave, what would you expect the result of the following statement to be?

[tt]UPDATE #Temp
SET @T = Test1 = 99, @T = Test2 = 47[/tt]

Yes, you guessed it, both Test1 and Test2 are set to 47.

-E²
 
Upvote 0 Downvote
oops!

[tt]@Variable = Expression, Column = [/tt][/color blue][tt]Expression[/tt][tt] -- <-- wrong![/tt]

[tt]@Variable = Expression, Column = [/tt][/color blue][tt]@Variable[/tt][tt] -- <-- right![/tt]
 
Upvote 0 Downvote
I noticed the same thing, but am still trying to figure out why? Does that make any sense?
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Group results into one row 3
2
Replies
37
Views
2K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
1K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
2K
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
1K
gmmastros
gmmastros
Andrzejek
  • Locked
  • Question
Field types question
Replies
9
Views
717
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top