How to split a column

1 Jul 2005 2:01 PM

Sidelo

I need MSSQL to split a column to different columns

The table had two columns:
ID    Message
1    Lastname: Siderius
                Firstname: Lourens
                Zipcode: 3083ER
2    Lastname: Test
                Firstname: Test
                Zipcode: Test

Then I use:

SELECT ID,

MAX(CASE WHEN Message LIKE 'Lastname: %' THEN SUBSTRING(Message,13,50) ELSE
'None' END),

MAX(CASE WHEN Message LIKE 'Firstname: %' THEN SUBSTRING(Message,12,50) ELSE
'None' END),

MAX(CASE WHEN Message LIKE 'Zipcode: %' THEN SUBSTRING(Message,12,50) ELSE
'None' END)

FROM Messages
GROUP BY ID

Then the result is:

(ID) (No column name)           (No column name)(No column name)
1     Siderius Firstname: Lourens Zipcode: 3083ER     None              None
2     Test Firstname: Test Zipcode: Test        None              None

But I want it to be:

(ID)     (No column name)    (No column name)      (No column name)
1    Siderius        Lourens        3083ER
2    Test        Test        test

Hope anyone could help me.
Thanxs Lourens.

1 Jul 2005 2:15 PM

Jens Süßmeyer

WHat about just replacing the Lastname,Firstname and Zipcode with '' ?

REPLACE(REPLACE(REPLACE(Message,FirstName,''),Message,LastName,''),Message,ZipCode,'')

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"Sidelo" <l.sider***@call2.nl> schrieb im Newsbeitrag
news:FF71BCE6-44A1-4C19-B682-031835026E11@microsoft.com...
>I need MSSQL to split a column to different columns
>
> The table had two columns:
> ID Message
> 1 Lastname: Siderius
>                Firstname: Lourens
>                Zipcode: 3083ER
> 2 Lastname: Test
>                Firstname: Test
>                Zipcode: Test
>
> Then I use:
>
> SELECT ID,
>
> MAX(CASE WHEN Message LIKE 'Lastname: %' THEN SUBSTRING(Message,13,50)
> ELSE
> 'None' END),
>
> MAX(CASE WHEN Message LIKE 'Firstname: %' THEN SUBSTRING(Message,12,50)
> ELSE
> 'None' END),
>
> MAX(CASE WHEN Message LIKE 'Zipcode: %' THEN SUBSTRING(Message,12,50) ELSE
> 'None' END)
>
> FROM Messages
> GROUP BY ID
>
> Then the result is:
>
> (ID) (No column name)    (No column name)(No column name)
> 1     Siderius Firstname: Lourens Zipcode: 3083ER None              None
> 2     Test Firstname: Test Zipcode: Test None              None
>
> But I want it to be:
>
> (ID) (No column name) (No column name)      (No column name)
> 1 Siderius Lourens 3083ER
> 2 Test Test test
>
> Hope anyone could help me.
> Thanxs Lourens.
>

1 Jul 2005 2:52 PM

Sidelo

Thanks but how do I do that when I try to use youâ€™re code it says I have to
use 3 statements one replace.

(Sorry Iâ€™m a new with SQL)

Show quote

"Jens SÃ¼ÃŸmeyer" wrote:

> WHat about just replacing the Lastname,Firstname and Zipcode with '' ?
>
> REPLACE(REPLACE(REPLACE(Message,FirstName,''),Message,LastName,''),Message,ZipCode,'')
>
>
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
> "Sidelo" <l.sider***@call2.nl> schrieb im Newsbeitrag
> news:FF71BCE6-44A1-4C19-B682-031835026E11@microsoft.com...
> >I need MSSQL to split a column to different columns
> >
> > The table had two columns:
> > ID Message
> > 1 Lastname: Siderius
> >                Firstname: Lourens
> >                Zipcode: 3083ER
> > 2 Lastname: Test
> >                Firstname: Test
> >                Zipcode: Test
> >
> > Then I use:
> >
> > SELECT ID,
> >
> > MAX(CASE WHEN Message LIKE 'Lastname: %' THEN SUBSTRING(Message,13,50)
> > ELSE
> > 'None' END),
> >
> > MAX(CASE WHEN Message LIKE 'Firstname: %' THEN SUBSTRING(Message,12,50)
> > ELSE
> > 'None' END),
> >
> > MAX(CASE WHEN Message LIKE 'Zipcode: %' THEN SUBSTRING(Message,12,50) ELSE
> > 'None' END)
> >
> > FROM Messages
> > GROUP BY ID
> >
> > Then the result is:
> >
> > (ID) (No column name)    (No column name)(No column name)
> > 1     Siderius Firstname: Lourens Zipcode: 3083ER None              None
> > 2     Test Firstname: Test Zipcode: Test None              None
> >
> > But I want it to be:
> >
> > (ID) (No column name) (No column name)      (No column name)
> > 1 Siderius Lourens 3083ER
> > 2 Test Test test
> >
> > Hope anyone could help me.
> > Thanxs Lourens.
> >
>
>
>

1 Jul 2005 3:04 PM

Jens Süßmeyer

Corrected

:REPLACE(REPLACE(REPLACE(Message,'FirstName',''),'LastName',''),'ZipCode','')

(some copy & Paster error :-)) but...

I reread your problem and found out that I had the right solution for the
wrong answer.

So you should head for the soltion from David and dig into to solve the
problem you are havingright now with it.

Sorry, Jens.

Show quote

"Sidelo" <l.sider***@call2.nl> schrieb im Newsbeitrag
news:7ED04FA9-C016-4BA4-B203-5C4693A6A358@microsoft.com...
> Thanks but how do I do that when I try to use you're code it says I have
> to
> use 3 statements one replace.
>
>
> (Sorry I'm a new with SQL)
>
>
> "Jens Süßmeyer" wrote:
>
>> WHat about just replacing the Lastname,Firstname and Zipcode with '' ?
>>
>> REPLACE(REPLACE(REPLACE(Message,FirstName,''),Message,LastName,''),Message,ZipCode,'')
>>
>>
>> HTH, Jens Suessmeyer.
>>
>> ---
>> http://www.sqlserver2005.de
>> ---
>>
>> "Sidelo" <l.sider***@call2.nl> schrieb im Newsbeitrag
>> news:FF71BCE6-44A1-4C19-B682-031835026E11@microsoft.com...
>> >I need MSSQL to split a column to different columns
>> >
>> > The table had two columns:
>> > ID Message
>> > 1 Lastname: Siderius
>> >                Firstname: Lourens
>> >                Zipcode: 3083ER
>> > 2 Lastname: Test
>> >                Firstname: Test
>> >                Zipcode: Test
>> >
>> > Then I use:
>> >
>> > SELECT ID,
>> >
>> > MAX(CASE WHEN Message LIKE 'Lastname: %' THEN SUBSTRING(Message,13,50)
>> > ELSE
>> > 'None' END),
>> >
>> > MAX(CASE WHEN Message LIKE 'Firstname: %' THEN SUBSTRING(Message,12,50)
>> > ELSE
>> > 'None' END),
>> >
>> > MAX(CASE WHEN Message LIKE 'Zipcode: %' THEN SUBSTRING(Message,12,50)
>> > ELSE
>> > 'None' END)
>> >
>> > FROM Messages
>> > GROUP BY ID
>> >
>> > Then the result is:
>> >
>> > (ID) (No column name)    (No column name)(No column name)
>> > 1     Siderius Firstname: Lourens Zipcode: 3083ER None
>> > None
>> > 2     Test Firstname: Test Zipcode: Test None              None
>> >
>> > But I want it to be:
>> >
>> > (ID) (No column name) (No column name)      (No column name)
>> > 1 Siderius Lourens 3083ER
>> > 2 Test Test test
>> >
>> > Hope anyone could help me.
>> > Thanxs Lourens.
>> >
>>
>>
>>

1 Jul 2005 2:20 PM

David Portas

SELECT id,
MAX(CASE WHEN message LIKE 'Lastname: %' THEN
SUBSTRING(message,13,CHARINDEX('Firstname:',message,13)-13) END),
MAX(CASE WHEN message LIKE 'Firstname: %' THEN
SUBSTRING(message,12,CHARINDEX('Zipcode:',message,12)-12) END),
MAX(CASE WHEN message LIKE 'Zipcode: %' THEN
SUBSTRING(message,12,50) END)
FROM messages
GROUP BY id ;

--
David Portas
SQL Server MVP
--

1 Jul 2005 2:48 PM

Sidelo

Thanks again,

But it still doesn't work.

I now get the result:
ID       (no column name)   (no column name)    (no column name)
1             Siderius                       Null                         Null
2               Test                          Null
Null

Show quote

"David Portas" wrote:

> SELECT id,
> MAX(CASE WHEN message LIKE 'Lastname: %' THEN
>   SUBSTRING(message,13,CHARINDEX('Firstname:',message,13)-13) END),
> MAX(CASE WHEN message LIKE 'Firstname: %' THEN
>   SUBSTRING(message,12,CHARINDEX('Zipcode:',message,12)-12) END),
> MAX(CASE WHEN message LIKE 'Zipcode: %' THEN
>   SUBSTRING(message,12,50) END)
> FROM messages
> GROUP BY id ;
>
> --
> David Portas
> SQL Server MVP
> --
>
>

1 Jul 2005 3:18 PM

David Portas

Sorry, I assumed you were aggregating across multiple rows, which
doesn't appear to be the case now I re-read your post. It really does
help if you can include DDL and sample INSERTs with your posts so that
we have enough info to reproduce the problem and test solutions. Here's
my assumption:

CREATE TABLE Messages (id INTEGER PRIMARY KEY, message VARCHAR(100) NOT
NULL)

INSERT INTO Messages (id, message)
SELECT
1, 'Lastname: Siderius
     Firstname: Lourens
     Zipcode: 3083ER'
UNION ALL
SELECT
2, 'Lastname: Test
     Firstname: Test
     Zipcode: Test' ;

And here's a solution:

SELECT id,
SUBSTRING(message,l_pos+10,f_pos-l_pos-10),
SUBSTRING(message,f_pos+11,z_pos-f_pos-11),
SUBSTRING(message,z_pos+9,100)
FROM
(SELECT id, message,
CHARINDEX('Lastname:',message) AS l_pos,
CHARINDEX('Firstname:',message) AS f_pos,
CHARINDEX('Zipcode:',message) AS z_pos
FROM messages) AS T ;

Result:

-------------------------------------------
1 Siderius      Lourens       3083ER
2 Test          Test          Test

--
David Portas
SQL Server MVP
--

5 Jul 2005 9:26 AM

Sidelo

Looks linke this one works,

Thanks!!!

Show quote

"David Portas" wrote:

> Sorry, I assumed you were aggregating across multiple rows, which
> doesn't appear to be the case now I re-read your post. It really does
> help if you can include DDL and sample INSERTs with your posts so that
> we have enough info to reproduce the problem and test solutions. Here's
> my assumption:
>
> CREATE TABLE Messages (id INTEGER PRIMARY KEY, message VARCHAR(100) NOT
> NULL)
>
> INSERT INTO Messages (id, message)
> SELECT
> 1, 'Lastname: Siderius
>      Firstname: Lourens
>      Zipcode: 3083ER'
> UNION ALL
> SELECT
> 2, 'Lastname: Test
>      Firstname: Test
>      Zipcode: Test' ;
>
> And here's a solution:
>
> SELECT id,
> SUBSTRING(message,l_pos+10,f_pos-l_pos-10),
> SUBSTRING(message,f_pos+11,z_pos-f_pos-11),
> SUBSTRING(message,z_pos+9,100)
> FROM
> (SELECT id, message,
>   CHARINDEX('Lastname:',message) AS l_pos,
>   CHARINDEX('Firstname:',message) AS f_pos,
>   CHARINDEX('Zipcode:',message) AS z_pos
>   FROM messages) AS T ;
>
> Result:
>
> -------------------------------------------
> 1 Siderius      Lourens       3083ER
> 2 Test          Test          Test
>
> --
> David Portas
> SQL Server MVP
> --
>
>