Re: Break up a field into multiple rows

From: Tibor Karaszi <tibor_not_pressed_ham_.karaszi_at_cornerstone.se>
Date: Sat, 26 Oct 2002 12:03:48 GMT
Message-ID: <Ewvu9.1454$1r1.44981_at_newsc.telia.net>

Todd,

Here's a "brute force" method, using some string manipulation and a UNION. Dialect is MS Transact-SQL. The CHARINDEX function find the position of the first expression within the second expression.

SELECT
 order_number

,sales_rep
,item
,quantity
,LEFT(territories, CHARINDEX(' ', territories))

FROM sales_orders
UNION ALL
SELECT
 order_number

,sales_rep
,item
,quantity
,RIGHT(territories, CHARINDEX(' ', territories))

FROM sales_orders

--
Tibor Karaszi


"Todd Benson" <toddb_at_spectralogic.com> wrote in message
news:97b61782.0210251514.7b0e9532_at_posting.google.com...


> I am trying to break down a string into 2 words and return them in


> separate rows (with the same data in the other fields).  So if I have

> a row with columns A,B,C where C is a string with 2 words (separated

> by a space), I want a query that will return A,B,C1 and A,B,C2.  For

> example:

>

> create table sales_orders (

>  order_number int not null primary key,

>  sales_rep varchar(20) not null,

>  item varchar(20) not null,

>  quantity int not null,

>  territories varchar(20) not null

> );

>

> insert into table sales_orders values ( 1, 'todd', 'widget', 5, 'east

> north' );

>

> I am trying to create a query that will return 2 rows:

>

> ( 1, 'todd', 'widget', 5, 'east' )

> and

> ( 1, 'todd', 'widget', 5, 'north' )

>

> Any ideas?

> Todd

Received on Sat Oct 26 2002 - 14:03:48 CEST