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
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...
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
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...
select *, dbo.split(bank.Banknavn,';') from bank.Banknavn
Dave
"Uri Dimant" <ur...@iscar.co.il> skrev i meddelelsen
news:eiX591WE...@TK2MSFTNGP03.phx.gbl...
Regards
Steen
"Uri Dimant" <ur...@iscar.co.il> skrev i meddelelsen
news:eiX591WE...@TK2MSFTNGP03.phx.gbl...
Take a look at CROSS APPLY in books on line.
--
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
select reverse('YLPPA SSORC')
:-)
Is an easy and quick solution worth paying for? :)
http://www.rac4sql.net/onlinehelp.asp?topic=236
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
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
>
> 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 :)
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...