Split strings how do i achieve this?

[steen maigaard]

Hi.

I have 2 tables:
1. source table has to fields Kobnr(varchar10), Bank(varchar50)

example of what it contains:

2214 Jyske Bank;Danske bank;Nordea

data in the Bank field are separated by semi-colon

i would like to put this information into the destination table so it looks
like this:

Destination table:

2214 Jyske Bank
2214 Danske Bank
2214 Nordea

i have to loop throug the source table and perform this on all records, but
i can't find out how to split the fields and insert them into the
destination table

.

Can anyone help me?

Cheers
Steen

[Dave Ballantyne]

There's no simple way,
You'll have to use a combination of charindex and substring to pull each
section.

Dave

[Uri Dimant]

Anith wrote this script

SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO

DECLARE @Ids VARCHAR(200)
SET @Ids = '5,33,229,1,22'
SELECT SUBSTRING(@Ids, n, CHARINDEX(',', @Ids + ',', n) - n)
from numbers where substring(','+@Ids,n,1)=','
AND n < LEN(@Ids) + 1

"steen maigaard" <s...@micropartner.dk> wrote in message
news:95ADE525-06BC-4F79...@microsoft.com...

[steen maigaard]

Thanks for the answers.

I have a function to split the strings, it looks like this

CREATE FUNCTION Split(@String varchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @ID int

SELECT @INDEX = 1, @ID = 1
WHILE @INDEX !=0

BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
SELECT @ID = @ID + 1
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END

If i call it with select * from split('a;b;c;d',';') it returns my 4 rows

If i call it with a table.field variable i get an odbc error

select * from split({bank.Banknavn},';')

is it not possible to use a table.field like this, what am i doing wrong?

cheers
Steen

[Uri Dimant]

Try

Do you use T-SQL or what?

declare @inList varchar(50)
set @inList='a;b;c;d',';'

SELECT *
FROM dbo.split(@InList)

"steen maigaard" <s...@micropartner.dk> wrote

in message news:6F4029C9-7549-4753...@microsoft.com...

[Dave Ballantyne]

Nearly there

select *, dbo.split(bank.Banknavn,';') from bank.Banknavn

Dave

[Dave Ballantyne]

Sorry ignore me, thats not gonna work

[steen maigaard]

I am using MS SQL 2000
Regards
Steen

"Uri Dimant" <ur...@iscar.co.il> skrev i meddelelsen
news:eiX591WE...@TK2MSFTNGP03.phx.gbl...

[steen maigaard]

I am using Microsoft SQL 2000

Regards
Steen

"Uri Dimant" <ur...@iscar.co.il> skrev i meddelelsen
news:eiX591WE...@TK2MSFTNGP03.phx.gbl...

[Joe Fawcett]

"steen maigaard" <s...@micropartner.dk> wrote in message

news:6F4029C9-7549-4753...@microsoft.com...

Take a look at CROSS APPLY in books on line.

--

Joe Fawcett (MVP - XML)
http://joe.fawcett.name

[Steve Dassin]

"Dave Ballantyne" <no_spam_8...@yahoo.com> wrote in message
news:%23D4sb1W...@TK2MSFTNGP06.phx.gbl...

> Nearly there
>
> select *, dbo.split(bank.Banknavn,';') from bank.Banknavn

select reverse('YLPPA SSORC')

:-)

www.beyondsql.blogspot.com

[Steve Dassin]

"steen maigaard" <s...@micropartner.dk> wrote in message

news:F9EAEB93-33D2-47AC...@microsoft.com...

>I am using MS SQL 2000
> Regards
> Steen

Is an easy and quick solution worth paying for? :)
http://www.rac4sql.net/onlinehelp.asp?topic=236

www.beyondsql.blogspot.com

[Paddy]

I raised a similar query earlier, and was put onto the very useful
website below.

Essentially it is very similar to Steens post earlier in this thread,
but also contains a fair bit of description.

http://www.sommarskog.se/arrays-in-sql-2000.html

Paddy

[Anith Sen]

>> is it not possible to use a table.field like this, what am i doing wrong?

For such problems in SQL, it is helpful to have a base table of sequentially
incrementing numbers. There are a variety of ways you can create one. See:
http://www.projectdmx.com/tsql/tblnumbers.aspx

Now, you can do:

SELECT Kobnr,
SUBSTRING( Bank, n, CHARINDEX( ';', Bank + ';', n ) - n )
FROM Nbrs JOIN tbl
ON SUBSTRING( ';' + Bank, n, 1 ) = ';'
AND n < LEN( Bank ) + 1 ;

And finally and more importantly, this is not a good way of representing
data in SQL. Unless you have a single value per column, you are bound to
have such problems. Restructure the schema so that you have one row per bank
name and declare an appropriate key and you'll be much happier in the long
run.

--
Anith

[Joe Fawcett]

"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:%23FWVinY...@TK2MSFTNGP04.phx.gbl...

>
> And finally and more importantly, this is not a good way of representing
> data in SQL. Unless you have a single value per column, you are bound to
> have such problems. Restructure the schema so that you have one row per
> bank name and declare an appropriate key and you'll be much happier in the
> long run.
>
> --
> Anith
>

I thought that's what he was trying to do :)

[steen maigaard]

Hi

Thanks for the help all of You.

There is a good reason that the data structure looks like it does,
unfortunately i can't avoid that, but i did not manage to make the Split
function work, so i'll have to dig more into it.

Regards
Steen

"Joe Fawcett" <joefa...@newsgroup.nospam> skrev i meddelelsen
news:%238neAEZ...@TK2MSFTNGP06.phx.gbl...