SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Best split function
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/15/2007 :  03:39:43  Show Profile  Reply with Quote
What about splitting multiple rows in a table, rather than a single CSV?

DECLARE	@Sample TABLE
(
	Col1 VARCHAR(6), 
	Col3 VARCHAR(200)
)

INSERT	@Sample
SELECT	'123', '125,124,126' UNION ALL
SELECT	'124', '127,21,245' UNION ALL
-- Edge condition data:
SELECT	'125', '' UNION ALL
SELECT	'126', ',1' UNION ALL
SELECT	'127', '2,' UNION ALL
SELECT	'128', ',3,'UNION ALL
SELECT	'129', NULL		-- Note: NULL is Ignored

-- SQL 2000 Version
SELECT	S.Col1,
	[Value] = SUBSTRING(',' + S.Col3 + ',', N.mb_int_ID + 1, 
		CHARINDEX(',', ',' + S.Col3 + ',',
			N.mb_int_ID + 1) - N.mb_int_ID - 1)
FROM	@Sample AS S
	-- Use a Tally table if you have one!!!
--	JOIN dbo.kk_MB_INT_Integer AS N
	JOIN
	(
		SELECT v0.n + v1.n + v2.n + v3.n AS mb_int_ID
		FROM
		(
			SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
			UNION ALL SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6 UNION ALL SELECT  7
			UNION ALL SELECT  8 UNION ALL SELECT  9 UNION ALL SELECT 10 UNION ALL SELECT 11
			UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
		)v0,
		(
			SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 
			UNION ALL SELECT  64 UNION ALL SELECT  80 UNION ALL SELECT  96 UNION ALL SELECT 112
			UNION ALL SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176
			UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
		) v1,
		(
			SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768
			UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792
			UNION ALL SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816
			UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
		) v2,
		(
			SELECT 0 n UNION ALL SELECT 4096
		) v3
	) AS N
		 ON N.mb_int_ID < LEN(',' + S.Col3 + ',')
		AND SUBSTRING(',' + S.Col3 + ',', N.mb_int_ID, 1) = ',' 

-- SQL 2005 version
SELECT	S.Col1,
	V.Value
FROM	@Sample AS S
	CROSS APPLY dbo.MySplitFunction(Col3, ',', NULL) AS V

(use your normal Split function)

Source material:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89817#335020
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90961#341398

Kristen

Edited by - Kristen on 10/15/2007 03:58:40
Go to Top of Page

pelegk2
Aged Yak Warrior

Israel
723 Posts

Posted - 09/18/2008 :  10:57:37  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
so whats actually is the best way to approcah a split on a string of NTEXT type?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2008 :  14:16:27  Show Profile  Reply with Quote
Almost 63,000 reads...not bad

But start a new post



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

camro
Starting Member

Colombia
1 Posts

Posted - 03/06/2009 :  11:05:12  Show Profile  Visit camro's Homepage  Reply with Quote
quote:
Is recursion still limited to only 32 levels in SQL Server 2005? If it is, BOOOOOM on the 33 parameter that needs to be split... (I could be wrong... might be different on CTE's which I've not had the pleasure to work with, yet.)


false... Default recursion limit according to MSDN is 100, but exist the MAXRECURSION option for levels of recursion from 0 to 32.767, set it to 0 and we will have ilimited levels of recursion...
MSDN:
quote:
Para evitar que se genere un bucle infinito, se puede limitar el número de niveles de recursividad permitidos para una instrucción determinada mediante el uso de la sugerencia MAXRECURSION y un valor de 0 a 32.767 en la cláusula OPTION de la instrucción INSERT, UPDATE, DELETE o SELECT. De esta manera, se puede controlar la ejecución de la instrucción hasta que se resuelva el problema de código que genera el bucle. El valor predeterminado para todo el servidor es 100. Si se especifica 0, no se aplica ningún límite. Sólo se puede especificar un valor de MAXRECURSION por instrucción


to me the best function is that use the CTE (Common Table Expression)

WITH Split(...) AS ....

only it missed the MAXRECURSION option in the query at end...


this is that I am using:


USE [MiBD]
GO
IF EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME = 'Split')
BEGIN
    DROP FUNCTION Split
END
GO
CREATE FUNCTION Split
(
  @Id        varchar(8000),
  @Separador varchar(8)   = ','
)
RETURNS TABLE
--WITH ENCRYPTION
AS
RETURN
(
  WITH SplitETC (Inicio, Fin)
    AS
  (
       SELECT 1,
              CHARINDEX(@Separador, @Id)
    UNION ALL
       SELECT Fin + LEN(@Separador),
              CHARINDEX(@Separador, @Id, Fin + LEN(@Separador))
         FROM SplitETC
        WHERE Fin > 0
  )
  SELECT RTRIM(LTRIM(SUBSTRING(@Id, Inicio, Largo))) Id
    FROM (SELECT Inicio,
                 CASE WHEN Fin IS NULL OR Fin = 0 THEN
                     LEN(@Id) + 1
                 ELSE
                     Fin
                 END  - Inicio  AS Largo
            FROM SplitETC OPTION (MAXRECURSION 0)) AS Split
)
GO



sorry for my bad english...


-------------------------------------------------------------------
De la programacion y otros demonios: http://www.preludioobsesivo.tk

Edited by - camro on 03/06/2009 11:22:29
Go to Top of Page

maliakkas
Starting Member

1 Posts

Posted - 08/01/2009 :  11:28:31  Show Profile  Reply with Quote
start writing my own based on yours so I thought I sould share...


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: <01-08-2009>
-- Description: <Splits Data into Table>
-- =============================================
CREATE FUNCTION Rp_SplitToTable
(
@t varchar(8000)
)
RETURNS @returnme TABLE
(
ID INT IDENTITY (1,1)
,COL1 VARCHAR(100)
,COL2 VARCHAR(100)
,COL3 VARCHAR(100)
,COL4 VARCHAR(100)
,COL5 VARCHAR(100)
,COL6 VARCHAR(100)
,COL7 VARCHAR(100)
)
AS
BEGIN
DECLARE @cnt INT
DECLARE @col1 VARCHAR(100)
DECLARE @col2 VARCHAR(100)
DECLARE @col3 VARCHAR(100)
DECLARE @col4 VARCHAR(100)
DECLARE @col5 VARCHAR(100)
DECLARE @col6 VARCHAR(100)
DECLARE @col7 VARCHAR(100)
DECLARE @colstop INT
DECLARE @colstopprevius INT
DECLARE @rowstop INT
DECLARE @rowprevius INT
DECLARE @rowSpliter VARCHAR(1)
DECLARE @colSpliter VARCHAR(1)
DECLARE @currentrow VARCHAR(700)


SET @cnt = 1
SET @rowSpliter = '~'
SET @colSpliter = '|'
SET @colstop = 0
SET @rowstop = 0
SET @rowprevius = 0

WHILE (CHARINDEX(@rowSpliter,@t, @rowprevius)>0)
BEGIN --ROW
SET @rowstop = CHARINDEX(@rowSpliter,@t, @rowprevius)
SET @currentrow = SUBSTRING(@t, @rowprevius, @rowstop - @rowprevius )
SET @colstopprevius = 0
SET @col1 = null
SET @col2 = null
SET @col3 = null
SET @col4 = null
SET @col5 = null
SET @col6 = null
SET @col7 = null
SET @colstop = CHARINDEX(@colSpliter, @currentrow, @colstopprevius)
IF (@colstop > 0 )
BEGIN --1
SET @col1 = SUBSTRING(@currentrow , @colstopprevius , @colstop - @colstopprevius )
SET @colstopprevius = ( SELECT @colstop + 1)

SET @colstop = CHARINDEX(@colSpliter, @currentrow, @colstopprevius)
IF (@colstop > 0 )
BEGIN --2
SET @col2 = SUBSTRING(@currentrow , @colstopprevius , @colstop - @colstopprevius )
SET @colstopprevius = ( SELECT @colstop + 1)

SET @colstop = CHARINDEX(@colSpliter, @currentrow, @colstopprevius)
IF (@colstop > 0 )
BEGIN --3
SET @col3 = SUBSTRING(@currentrow , @colstopprevius , @colstop - @colstopprevius )
SET @colstopprevius = ( SELECT @colstop + 1)

SET @colstop = CHARINDEX(@colSpliter, @currentrow, @colstopprevius)
IF (@colstop > 0 )
BEGIN --4
SET @col4 = SUBSTRING(@currentrow , @colstopprevius , @colstop - @colstopprevius )
SET @colstopprevius = ( SELECT @colstop + 1)

SET @colstop = CHARINDEX(@colSpliter, @currentrow, @colstopprevius)
IF (@colstop > 0 )
BEGIN --5
SET @col5 = SUBSTRING(@currentrow , @colstopprevius , @colstop - @colstopprevius )
SET @colstopprevius = ( SELECT @colstop + 1)

SET @colstop = CHARINDEX(@colSpliter, @currentrow, @colstopprevius)
IF (@colstop > 0 )
BEGIN --6
SET @col6 = SUBSTRING(@currentrow , @colstopprevius , @colstop - @colstopprevius )
SET @colstopprevius = ( SELECT @colstop + 1)

SET @colstop = CHARINDEX(@colSpliter, @currentrow, @colstopprevius)
IF (@colstop > 0 )
BEGIN --7
SET @col7 = SUBSTRING(@currentrow , @colstopprevius , @colstop - @colstopprevius )

END --7
END --6
END --5
END --4
END --3
END --2
END --1

INSERT INTO @returnme
SELECT @col1, @col2, @col3, @col4, @col5, @col6, @col7

SET @rowprevius = (SELECT @rowstop +1)
END --ROW

RETURN
END
GO
Go to Top of Page

13ina
Starting Member

5 Posts

Posted - 10/26/2009 :  20:09:46  Show Profile  Reply with Quote
I'm a complete newbie to MS SQL, and I really need to take an email field from a table, split it, and display it within the results as three parts. Does anyone know how I could do this?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 10/26/2009 :  21:20:36  Show Profile  Reply with Quote
13ina,

Please start a new thread for your question


KH
Time is always against us

Go to Top of Page

jezza101
Starting Member

1 Posts

Posted - 02/24/2010 :  12:11:29  Show Profile  Reply with Quote
Just a quick note to say if you haven't tried doing this via a CLR function then it's worth doing. I roll a new function into our db assemblies any time string manipulation is required, life's too short for SQL's string functions.

Not only is very quick but you just explode your input column with the appropriate c# function without any crazy string manipulation required.

Fast, easy and reliable.

Of course, we all like a SQL challenge from time to time though!
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 02/25/2010 :  10:25:35  Show Profile  Reply with Quote
So, post your CLR function code and explain how it is superior to the SQL code.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

tofo
Starting Member

1 Posts

Posted - 06/04/2010 :  04:22:53  Show Profile  Reply with Quote
What about using XML parsing to split like this?


-- Create a comma delimited string for testing
declare @str varchar(max)
select @str = '02315A, 02311B, 02312A, 00889CS, 00093A, 00854DG, 00854DJ'

-- XML tag the string by replacing commas with </v><v> tags
declare @xml xml
select @xml = cast('<v>'+ replace(@str,',','</v><v>') + '</v>' as xml)

-- Finally select values from nodes <v> and trim at the same time
select ltrim(rtrim(mynode.value('.[1]', 'nvarchar(12)'))) as Code
from (select @xml doc) xx
cross apply doc.nodes('/v') (mynode)


Go to Top of Page

sgartner
Starting Member

USA
1 Posts

Posted - 06/04/2010 :  17:45:31  Show Profile  Reply with Quote
quote:
Originally posted by tofo

What about using XML parsing to split like this?



It's an interesting thought, but you must be 100% sure there are no XML parse-able characters in the incoming data (particularly "<" and ">"). You can ensure the data won't get in the way by using CDATA blocks like this:


select @xml = cast('<v><![CDATA['+ replace(@str,',',']]></v><v><![CDATA[') + ']]></v>' as xml)


I'd love to see a speed comparison between this method and some of the others suggested in this thread.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 06/04/2010 :  18:42:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
Excellent timing:

http://www.sqlshare.com/SplittingDelimitedStrings_774.aspx
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 06/05/2010 :  06:45:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Also see
http://beyondrelational.com/blogs/community/archive/2010/05/27/delimited-string-tennis-again-the-final-volley.aspx

and this, to avoid some pitfalls using XML
http://sqlblog.com/blogs/adam_machanic/archive/2010/01/12/t-sql-tuesday-002-is-it-xml-or-not.aspx



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

Edited by - SwePeso on 06/05/2010 06:46:01
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/02/2011 :  10:12:26  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
I know this is an old thread... but I think this article is very appropriate and should linked here

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Corey

I Has Returned!!
Go to Top of Page

vmvadivel
Yak Posting Veteran

India
69 Posts

Posted - 10/02/2011 :  03:08:38  Show Profile  Visit vmvadivel's Homepage  Reply with Quote
I know I am late to the party :) Thought would share a blog post of mine relating to this topic as well here - http://vadivel.blogspot.com/2011/10/how-to-split-delimited-string-values-in.html

Best Regards
Vadivel

http://vadivel.blogspot.com
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/02/2011 :  04:44:15  Show Profile  Reply with Quote
The XML method was already posted in this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=3#571876
Go to Top of Page

DevAffair
Starting Member

Israel
1 Posts

Posted - 04/02/2012 :  06:57:44  Show Profile  Reply with Quote
http://www.devaffair.com/2011/09/split-string-in-sql-server.html

This link will give you the function I'm using. for me it worked perfectly..

www.DevAffair.com
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.41 seconds. Powered By: Snitz Forums 2000