copy som data from one field split it up into 3 new fields

Hey there
I have to copy som data from one field and split it up into 3 seperat new
fields in the same table...
Eks.:
---
Data column Artis (Old field)
---
Artist
Larsen, Kim & Kjukken
Larsen, Kim
Larsen, Kim - Kjukken
---
Three new data columns
---
Firstname Lastname Band
Kim Larsen & Kjukken
Kim Larsen
Kim Larsen - Kjukken
My SQL Statement looks like this now, I just have to add the Band column and
the other - and & checks:
UPDATE Test_Products
SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
LEFT(artist, CHARINDEX(' ',artist) - 1)
ELSE
RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
END,
Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
ELSE
LEFT(artist, CHARINDEX(',',artist) - 1)
END
I hope hearing from you soon, because my project is totally jamed up until I
get this working and done.
Regards,
Lucien
Mvh
DC
--
Greetings ecoderHi
I'd prefer doing such thing on the client
create table #test
(
col varchar (50)
)
insert into #test values ('Larsen, Kim & Kjukken')
insert into #test values ('Larsen, Kim')
insert into #test values ('Larsen, Kim - Kjukken')
select LastName, FirstName, coalesce(Band,FirstName) Band
from (
select
col,
substring(col,1,Comma-1) LastName,
substring(col,Comma+1,Spce-4) FirstName,
nullif(substring(col,Spce+4,40),'') Band
from (
select
col,
charindex(',',col) Comma,
charindex(' ',col+space(1),charindex(',',col)) Spce
from #test
) D
) SplitNames
"ecoder" <ecoder@.discussions.microsoft.com> wrote in message
news:1B244528-6EBE-42DF-B1DB-3194BB9A2D7E@.microsoft.com...
> Hey there
> I have to copy som data from one field and split it up into 3 seperat new
> fields in the same table...
> Eks.:
> ---
> Data column Artis (Old field)
> ---
> Artist
> Larsen, Kim & Kjukken
> Larsen, Kim
> Larsen, Kim - Kjukken
> ---
> Three new data columns
> ---
> Firstname Lastname Band
> Kim Larsen & Kjukken
> Kim Larsen
> Kim Larsen - Kjukken
> --
> My SQL Statement looks like this now, I just have to add the Band column
> and
> the other - and & checks:
> UPDATE Test_Products
> SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> LEFT(artist, CHARINDEX(' ',artist) - 1)
> ELSE
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> END,
> Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> ELSE
> LEFT(artist, CHARINDEX(',',artist) - 1)
> END
> I hope hearing from you soon, because my project is totally jamed up until
> I
> get this working and done.
> Regards,
> Lucien
> Mvh
> DC
> --
> Greetings ecoder|||See if this helps:
select
c1,
left(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' ') - 1) as firstname,
left(c1, charindex(',', c1) - 1) as lastname,
parsename(stuff(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' '), 1, '.'), 1) as band
from
(
select 'Larsen, Kim & Kjukken'
union all
select 'Larsen, Kim'
union all
select 'Larsen, Kim - Kjukken'
) as t1(c1)
go
AMB
"ecoder" wrote:

> Hey there
> I have to copy som data from one field and split it up into 3 seperat new
> fields in the same table...
> Eks.:
> ---
> Data column Artis (Old field)
> ---
> Artist
> Larsen, Kim & Kjukken
> Larsen, Kim
> Larsen, Kim - Kjukken
> ---
> Three new data columns
> ---
> Firstname Lastname Band
> Kim Larsen & Kjukken
> Kim Larsen
> Kim Larsen - Kjukken
> --
> My SQL Statement looks like this now, I just have to add the Band column a
nd
> the other - and & checks:
> UPDATE Test_Products
> SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> LEFT(artist, CHARINDEX(' ',artist) - 1)
> ELSE
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> END,
> Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> ELSE
> LEFT(artist, CHARINDEX(',',artist) - 1)
> END
> I hope hearing from you soon, because my project is totally jamed up until
I
> get this working and done.
> Regards,
> Lucien
> Mvh
> DC
> --
> Greetings ecoder|||Hi ,Alejandro
Hehe, I have faced the same "problem". A Band column for Kim is NULL.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:22AF2E13-05F9-4DCA-BA56-D50D2A2A1031@.microsoft.com...
> See if this helps:
> select
> c1,
> left(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
> ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' ') - 1) as firstname,
> left(c1, charindex(',', c1) - 1) as lastname,
> parsename(stuff(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex('
> ',
> ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' '), 1, '.'), 1) as band
> from
> (
> select 'Larsen, Kim & Kjukken'
> union all
> select 'Larsen, Kim'
> union all
> select 'Larsen, Kim - Kjukken'
> ) as t1(c1)
> go
>
> AMB
> "ecoder" wrote:
>|||Hi Uri
When I run it in query analyzer the field without a band looks like this:
Firstname Lastname Band
Kim Larsen Kim
Why is that and what should I change to correct this?
Regards,
ecoder
"Uri Dimant" wrote:

> Hi
> I'd prefer doing such thing on the client
>
> create table #test
> (
> col varchar (50)
> )
> insert into #test values ('Larsen, Kim & Kjukken')
> insert into #test values ('Larsen, Kim')
> insert into #test values ('Larsen, Kim - Kjukken')
>
> select LastName, FirstName, coalesce(Band,FirstName) Band
> from (
> select
> col,
> substring(col,1,Comma-1) LastName,
> substring(col,Comma+1,Spce-4) FirstName,
> nullif(substring(col,Spce+4,40),'') Band
> from (
> select
> col,
> charindex(',',col) Comma,
> charindex(' ',col+space(1),charindex(',',col)) Spce
> from #test
> ) D
> ) SplitNames
>
> "ecoder" <ecoder@.discussions.microsoft.com> wrote in message
> news:1B244528-6EBE-42DF-B1DB-3194BB9A2D7E@.microsoft.com...
>
>|||Uri,
Let him / her to decide what to do with null values. The help you provided
is a good start, the rest is up to him / her.
AMB
"Uri Dimant" wrote:

> Hi ,Alejandro
> Hehe, I have faced the same "problem". A Band column for Kim is NULL.
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:22AF2E13-05F9-4DCA-BA56-D50D2A2A1031@.microsoft.com...
>
>|||Lookup LTRIM(),RTRIM() functions in the BOL
"ecoder" <ecoder@.discussions.microsoft.com> wrote in message
news:EABB8DE8-1DD3-44E0-875E-51B775A85038@.microsoft.com...
> Hi Uri
> When I run it in query analyzer the field without a band looks like this:
> Firstname Lastname Band
> Kim Larsen Kim
> Why is that and what should I change to correct this?
> Regards,
> ecoder
>
> "Uri Dimant" wrote:
>|||It's all working well becides when to band column is null
This is a urgent one...
If the band column is empty it copies the firstname Kim into the band column
.
What should I change....?
And thanks Uri and Alejandro for your help.
Greetings ecoder
"Alejandro Mesa" wrote:
> Uri,
> Let him / her to decide what to do with null values. The help you provided
> is a good start, the rest is up to him / her.
>
> AMB
> "Uri Dimant" wrote:
>|||If you are talking about Uri's solution, then use an empty string for the
second argument of the "coalesce" function or do not use the "coalesce"
function at all.

> select LastName, FirstName, coalesce(Band,FirstName) Band
select LastName, FirstName, coalesce(Band,'') Band
...
-- or
select LastName, FirstName, Band
...
AMB
"ecoder" wrote:
> It's all working well becides when to band column is null
> This is a urgent one...
> If the band column is empty it copies the firstname Kim into the band colu
mn.
> What should I change....?
> And thanks Uri and Alejandro for your help.
> --
> Greetings ecoder
>
> "Alejandro Mesa" wrote:
>|||God morning...
I have one last question though. I have to update an existing database table
with lots off records.
How do I change your or Uri examples to UPDATE instead of INSERT.
Sorry I'm not that hardcore in T-SQL!
:-)
But i'm trying...
Hope hearing from you.
Regards,
ecoder
"Alejandro Mesa" wrote:
> If you are talking about Uri's solution, then use an empty string for the
> second argument of the "coalesce" function or do not use the "coalesce"
> function at all.
>
> select LastName, FirstName, coalesce(Band,'') Band
> ...
> -- or
> select LastName, FirstName, Band
> ...
>
> AMB
>
> "ecoder" wrote:
>