string with data and column separator

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

string with data and column separator

Author

Topic

djfnt
Starting Member

2 Posts

Posted - 2003-05-06 : 10:57:24

i've a problem...

i have a string that contain some data packed like this

'001-Name-Try*002-Name2-Try2*003-Name3-Try3'

i've packed this string with 2 separator character
for the columns separator i've used '-' and for the row '*'

i want to insert this data in a table
that has 3 columns (CODE,NAME,DESC)
to have the data in the string inserted on the table like this

|CODE| NAME | DESC
001 Name Try
002 Name2 Try2

I could do this in ASP but I must do this in a Store procedure

I found a topic explianing what to do but it has only one separator
for the colums

how can I do?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-06 : 11:36:56

Pretty simple really...


declare @string varchar(2000), @row varchar(1), @col varchar(1)
select @string = '001-Name-Try*002-Name2-Try2*003-Name3-Try3',
       @row = '*',
       @col = '-'

select
    left(row,charindex(@col,row)-1) as Code,
    substring(row,charindex(@col,row)+1,charindex(@col,row,charindex(@col,row)+1)-charindex(@col,row)-1) as [Name],
    reverse(left(reverse(row),charindex(@col,reverse(row))-1)) as [Desc]
from (
    select
        nullif(substring(@row+@string+@row,n,charindex(@row,@row+@string+@row,n)-n),'') as row
    from
        dbo.tally
    where
        n<=datalength(@row+@string+@row) and
        n-datalength(@row)>0 and
        substring(@row+@string+@row,n-datalength(@row),datalength(@row))=@row and
        charindex(@row,@row+@string+@row,n)-n>0 ) a

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-06 : 11:53:11

quote:

Pretty simple really...

I think you just blew his proverbial mind....

great stuff

Brett

8-)

EDIT: I don't think s/he knows what a tally table is though...

Edited by - x002548 on 05/06/2003 11:55:29

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-06 : 19:32:17

And to understand the best way to create a tally table.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25652

djfnt
Starting Member

2 Posts

Posted - 2003-05-07 : 04:43:41

10q Jay White but what structure must have the tally
table in this case

cause i think I have to declare and create a table
but in next post i don't understand well how to create

can you show how can i create my tally table
wich is useful in my case..

10q 1 more

quote:

Pretty simple really...


declare @string varchar(2000), @row varchar(1), @col varchar(1)
select @string = '001-Name-Try*002-Name2-Try2*003-Name3-Try3',
       @row = '*',
       @col = '-'

select
    left(row,charindex(@col,row)-1) as Code,
    substring(row,charindex(@col,row)+1,charindex(@col,row,charindex(@col,row)+1)-charindex(@col,row)-1) as [Name],
    reverse(left(reverse(row),charindex(@col,reverse(row))-1)) as [Desc]
from (
    select
        nullif(substring(@row+@string+@row,n,charindex(@row,@row+@string+@row,n)-n),'') as row
    from
        dbo.tally
    where
        n<=datalength(@row+@string+@row) and
        n-datalength(@row)>0 and
        substring(@row+@string+@row,n-datalength(@row),datalength(@row))=@row and
        charindex(@row,@row+@string+@row,n)-n>0 ) a

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources