function or SP or cursor question

Question

question

anamika asked Jun 3, '10

function or SP or cursor question

Actually I have table called fruits which has column name fruits_type having records like record1:apple banana & mango record2:apple chickoo & peach It has fruit id as identity column. I should split data for first record like row1-apple row2-banana & mango and in similar ways for second record it sud like row1-apple row2-chickoo & peach Here delimiter is char(30)+char(10) I tried to write split function as follow:

declare @string 
declare @delimiter
select @string=fruits_type 
from fruits 
where fruit_id=1 
set @delimiter=char(30)+char(10)

There are lot of records like this in that table. so final result sud be like

sr.no fruit_tye 
1   apple 
2   banana & mango
3   apple
4   chickoo & peach

This record has to be fetched in temp table but duplicate records have to be eliminated. so final temp table sud be like

1 apple
2 banana & mango
3 chicko & peach

I think we can write function or within stored procedure,we can write cursor for row by row processing.But I don't know to write complex query.Please it's urgent.Can anyone help me with the code.It's too urgent.

sql-server-2005 t-sql

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-06-03T23:29:31Z

Oleg answered Jun 3, '10

Let me assume that all your fruits_type records are just like your describe with the only exception that the delimiter is char(13) + char(10) and not char(30)+char(10). This is because char(30) does not make any sense as a delimiter while char(13) + char(10) is simply a normal line break (char(13) is a carriage return and char(10) is linefeed). To reproduce your scenario, I will create table named dbo.fruits and insert some data there:

create table dbo.fruits 
(
    fruit_id int identity(1, 1) not null
    constraint PK_fruits primary key clustered,
    fruits_type varchar(100) not null
);
go

insert into dbo.fruits (fruits_type) 
values ('apple' + char(13) + char(10) + 'banana & mango');
insert into dbo.fruits (fruits_type) 
values ('apple' + char(13) + char(10) + 'chickoo & peach');
insert into dbo.fruits (fruits_type) 
values ('mango' + char(13) + char(10) + 'fishheads & broccoli');
insert into dbo.fruits (fruits_type) 
values ('pear' + char(13) + char(10) + 'naga & jolokia');
go

Now, selecting data from this table will yield:

fruit_id    fruits_type
----------- ------------
1           apple
            banana & mango

2           apple
            chickoo & peach

3           mango
            fishheads & broccoli

4           pear
            naga & jolokia

To get the data like you want you can then use the following query below (you can insert the results into temp table if you want, but I will just show the results of the select instead. Here is the query:

with fruits (fruits_type) as 
(
    select
        case
            when b.seed = 1 then left(a.fruits_type, charindex(char(13), a.fruits_type, 1) - 1)
            else substring(a.fruits_type, charindex(char(13), a.fruits_type, 1) + 2, 100)
        end fruits_type
        from dbo.fruits a cross join (select 1 seed union select 2) b
)
    select 
        row_number() over (order by fruits_type) the_id,
        fruits_type
        from fruits
        group by fruits_type;

And here are the results:

the_id               fruits_type
-------------------- ---------------------
1                    apple
2                    banana & mango
3                    chickoo & peach
4                    fishheads & broccoli
5                    mango
6                    naga & jolokia
7                    pear

Hope this helps.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question