sorting some data...

[Mario C]

ok..
here goes..

I have a name field in my table called "bname"
all of the data in this field has full names in it, spaces are
separated by a "+"

ie. "john+doe"

i have two other fields that are currently NULL. "First_name " and
"Last_name"
now my question is obvious.

I need to populate "First_name " with all of the data in "bname" to the
left of the "+"

and likewise to the Right of the "+" in "Last_name"

i sort of know how i would do this in ASP... but im trying to make
this a DTS package... so i was wondering if this could be done in SQL

Thanks for any help! :D

Mario C.

[DHatheway]

Re: sorting some data...

If bname, first_name and last_name are all columns in the same table, I'm
not sure you'd want to do this in a DTS package. This works:

create table names
(names_id int not null,
first_name varchar(24) null,
last_name varchar(24) null,
bname varchar(24) null,
constraint pk_names primary key clustered (names_id))
go
create function get_first_name (@a_name varchar(24)) returns varchar(24)
as
begin
declare @pos int
declare @tmpstr varchar(24)
set @pos = charindex('+',@ a_name)
set @tmpstr = left(@a_name,@p os-1)
return @tmpstr
end
go
insert names values (1,null,null,'h arry+potter')
go
select * from names
go
update names
set first_name = dbo.get_first_n ame(bname)
go
select * from names
go

A get_last_name function would be similar and you could update both fields
at once, of course.

Of course, you might want to put some defensive code into your function
(evaluate whether the bname field is null, zero length, missing the '+',
etc). Of course, if this is a one-time thing and you're sure of the quality
of your data, you might be able to skip the defensive code.

"Mario C" <philo_surfer_1 981@yahoo.com> wrote in message
news:c8db25a5.0 312121305.66ae2 399@posting.goo gle.com...[color=blue]
> ok..
> here goes..
>
> I have a name field in my table called "bname"
> all of the data in this field has full names in it, spaces are
> separated by a "+"
>
> ie. "john+doe"
>
> i have two other fields that are currently NULL. "First_name " and
> "Last_name"
> now my question is obvious.
>
> I need to populate "First_name " with all of the data in "bname" to the
> left of the "+"
>
> and likewise to the Right of the "+" in "Last_name"
>
> i sort of know how i would do this in ASP... but im trying to make
> this a DTS package... so i was wondering if this could be done in SQL
>
> Thanks for any help! :D
>
> Mario C.[/color]

[mario c]

Re: sorting some data...

ok.. that works..

but i still need to take the current data in the table and update all
"First_name " and "Last_name" fields.

the "bname" field contains the contains the data that needs to be
parsed.

I need to do it to this table, i am currently re writing the script that
inserts the data so it will correctly from here on out.. but as of now i
need to make the changes to the current data.

thanks again... still plugin away. i will see if i can work with what
you gave me... and pick it apart to come up with something.. but if you
can help it would be greatly appreciated.

:D
Mario.



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

[Simon Hayes]

Re: sorting some data...


"mario c" <philo_surfer_1 981@yahoo.com> wrote in message
news:3fda4ad0$0 $199$75868355@n ews.frii.net...[color=blue]
> ok.. that works..
>
> but i still need to take the current data in the table and update all
> "First_name " and "Last_name" fields.
>
> the "bname" field contains the contains the data that needs to be
> parsed.
>
> I need to do it to this table, i am currently re writing the script that
> inserts the data so it will correctly from here on out.. but as of now i
> need to make the changes to the current data.
>
> thanks again... still plugin away. i will see if i can work with what
> you gave me... and pick it apart to come up with something.. but if you
> can help it would be greatly appreciated.
>
> :D
> Mario.
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

This should work for you, assuming that bname is always in the format
'first_name+las t_name':

update dbo.MyTable
set first_name = left(bname, charindex('+', bname) - 1),
last_name = right(bname, len(bname) - charindex('+', bname))

Simon

[mario c]

Re: sorting some data...



Cool.. that worked perfectly!

Looks like i need to learn what CharIndex does... :D

Thanks Simon.


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