Split Function - SQL Server Forums

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)

Split Function

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2008-02-06 : 22:21:08

I've been reading an article about creating a split function which will take a comma delimited string with id's as input parameter for a SQL query. I need to pass a comma delimmited string of records IDs from checkboxes on a .aspx page and return selected records. I'm not getting any errors from the function or stored procedure but it returns every record in the table regardless of how many Ids I pass to it.

Split function:

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = N'Split'
)
DROP FUNCTION Split
GO
CREATE FUNCTION dbo.Split
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS

BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
GO

Sproc:

CREATE PROCEDURE up_ExportSelectedDirectors
@p_selectedDirectors NVARCHAR(4000)
AS
BEGIN

SELECT * FROM v_SearchResultsDirectors
WHERE IDDir in (SELECT IDDir FROM split(@p_selectedDirectors, ','))
END
GO

Even if I manually run something like this, I get all records instead of just the two.

SELECT * FROM v_SearchResultsDirectors
WHERE IDDir in (SELECT IDDir FROM split('1234,4321', ','))

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-06 : 22:42:14

Do you get the expected results from these statements?

SELECT * FROM v_SearchResultsDirectors
WHERE IDDir in (1234,4321)

SELECT IDDir FROM split('1234,4321', ',')

EDIT:
Actually, your function doesn't return [IDDir] column, it returns [Item] so your statement should blow up, not return all the rows???

Be One with the Optimizer
TG

evanburen
Posting Yak Master

167 Posts

Posted - 2008-02-06 : 23:15:35

Strangely, it didn't blow up - it just didn't work. I changed Item to IDDir in the function and it works now. Thanks a bunch.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 01:25:30

you can also make use of these well tested "split" function

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-11 : 04:47:11

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

Dimon
Starting Member

3 Posts

Posted - 2008-09-09 : 05:04:47

How can I use the split-function in a real sql-statement like:

select (select * from split(my_column), ',') from my_table

THX

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 07:42:12

SELECT t.*
FROM Table1 AS t
INNER JOIN dbo.fnSplit() AS x ON x.Col1 = t.Col1

E 12°55'05.63"
N 56°04'39.26"

Dimon
Starting Member

3 Posts

Posted - 2008-09-10 : 06:54:53

Thank you for answer but I have to use that function for the whole table and not for some items

The parameter in the function has to be a column from table
If write this (original)

SELECT P.institut
FROM tbl_person AS P
INNER JOIN dbo.sp_Split(P.institut,'') AS x ON x.Item = P.institut

ERROR: "P.institut" couldnt be joined

Without P. I get error -> unknown column "institut"

THX

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-10 : 08:31:26

Try

SELECT P.institut
FROM tbl_person AS P
where ','+@institut+',' like '%,'+cast(Item as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

Dimon
Starting Member

3 Posts

Posted - 2008-09-10 : 10:37:30

quote:
Originally posted by madhivanan

Thanx

I have got another task
look

column 'institut' contains data like this
'TZ : 01.04.2003 - 30.09.2003 Gastwissenschaftler¶TZ : 10.05.2004 - 30.04.2006 Gastwissenschaftler¶TZ : 01.04.2006 - 31.12.2007 Gastwissenschaftler'

I have to split this string with '¶'

TZ : 01.04.2003 - 30.09.2003 Gastwissenschaftler
TZ : 10.05.2004 - 30.04.2006 Gastwissenschaftler
TZ : 01.04.2006 - 31.12.2007 Gastwissenschaftler

I have more then 35.000 Items in column institut

THX

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-11 : 03:51:28

quote:
Originally posted by Dimon

quote:
Originally posted by madhivanan

Thanx

I have got another task
look

column 'institut' contains data like this
'TZ : 01.04.2003 - 30.09.2003 Gastwissenschaftler¶TZ : 10.05.2004 - 30.04.2006 Gastwissenschaftler¶TZ : 01.04.2006 - 31.12.2007 Gastwissenschaftler'

I have to split this string with '¶'

TZ : 01.04.2003 - 30.09.2003 Gastwissenschaftler
TZ : 10.05.2004 - 30.04.2006 Gastwissenschaftler
TZ : 01.04.2006 - 31.12.2007 Gastwissenschaftler

I have more then 35.000 Items in column institut

THX

Search for Split function here

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources