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
 Transact-SQL (2000)
 Slice a string
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 11/26/2005 :  14:52:05  Show Profile  Reply with Quote
:) :)
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/27/2005 :  01:54:10  Show Profile  Reply with Quote
"but now it's useless, no?"

Hey Spirit, where were you when your Team needed you?

"I can now see the way to go is in the update statement with multiple values in the set statement"

That's the thing to get your head around. Cursors and Loops and linear programming are to avoided with SQL, and instead SET based stuff is SO much faster [in general terms, of course].

The first time I started using a table of numbers (1,2,3,...) to JOIN to to provide a route to the answer I thought "This is nuts, way too hard to understand and it surely can't be significantly quicker" - but I now know it can easily be an order of magnitude quicker.

Now I think more like that e.g. - "I want to add all the dates for next month to a Calendar table" - JOIN to my "numbers" table and I can insert them in one go, doing a LOOP would be much slower.

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/27/2005 :  02:36:29  Show Profile  Reply with Quote
Here's the code to generate a SELECT and an UPDATE to "split" a fixed number of fields.

Note that this generates "blank string" values where there is a trailing delimiter and NO following value, and NULL values if a trailing delimiter (and its value, obviously!) are missing

-- Based on code by http://www.umachandar.com/technical/SQL70Scripts/Main10.htm
-- Modified by Kristen 2004/2005

DECLARE	@pos		int,
	@numtokens	int,
	@field		varchar(128),
	@field2		varchar(128),
	@strDelimiter	varchar(1),
	@previdx	varchar(255),
	@maxtokens	int


-- Set these parameters as appropriate, and run down to "TEST RIG"
SELECT	@maxtokens = 5,		-- Number of tokens, per row, to be split
	@field = 'MyCol',	-- Name of Column to split
	@strDelimiter = '|'	-- Delimiter - e.g. CHAR(9) or '-'

SELECT	@field2 = @field + ' + ''' + @strDelimiter + ''''	-- Shorthand! for @field plus delimiter

-- SELECT stuff
SELECT	@numtokens = 0,	-- Initialisation
	@previdx = '0'	--  charindex('|', @str, 1)
PRINT	'SELECT'
WHILE(@numtokens < @maxtokens)
BEGIN
	PRINT	CASE WHEN @numtokens = 0 THEN '  ' ELSE ', ' END
		+ '[COLUMN_' + CONVERT(varchar(20), @numtokens+1) + '] = '
		+ 'substring(' + @field2 + ', ' + CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END
		+ ' + 1, charindex(''' + @strDelimiter + ''', ' + @field2 + ', '
		+ CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END + ' + 1)'
	        + ' - ' + CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END + ' - 1 )'
	SELECT	@previdx = ' charindex(''' + @strDelimiter + ''', ' + @field2
			+ CASE WHEN @numtokens = 0 THEN '' ELSE ', ' + @previdx + ' + 1' END + ')',
		@numtokens = @numtokens + 1
END
PRINT 'FROM	dbo.MyTable'

-- UPDATE stuff
SELECT	@numtokens = 0	-- Initialisation
PRINT	'UPDATE U'
PRINT	'SET'
WHILE(@numtokens < @maxtokens)
BEGIN
	IF @numtokens = 0
	BEGIN
		PRINT CHAR(9) + '  @I'  + CONVERT(varchar(20), @numtokens+1) 
			+ ' = CHARINDEX(''' + @strDelimiter + ''', ' + @field2 + ')'
		PRINT CHAR(9) + ', [COLUMN_' + CONVERT(varchar(20), @numtokens+1) 
			+ '] = LEFT(' + @field + ', @I'  
			+ CONVERT(varchar(20), @numtokens+1) + '-1)'
	END
	ELSE
	BEGIN
		PRINT CHAR(9) + ', @I'  + CONVERT(varchar(20), @numtokens+1) 
			+ ' =  NullIf(CHARINDEX(''' + @strDelimiter + ''', ' 
			+ @field2 + ', @I'  + CONVERT(varchar(20), @numtokens) 
			+ '+1), 0)'
		PRINT CHAR(9) + ', [COLUMN_' + CONVERT(varchar(20), @numtokens+1) 
			+ '] = SUBSTRING(' + @field + ', @I'  + CONVERT(varchar(20), @numtokens) 
			+ '+1, @I'  + CONVERT(varchar(20), @numtokens+1) + '-@I'  
			+ CONVERT(varchar(20), @numtokens) + '-1)'
	END
	SELECT @numtokens = @numtokens + 1
END
PRINT 'FROM	dbo.MyTable AS U'



-- TEST RIG:
DECLARE @MyTable TABLE
(
	MyCol varchar(50), 
	MyCol1 varchar(10), 
	MyCol2 varchar(10), 
	MyCol3 varchar(10), 
	MyCol4 varchar(10), 
	MyCol5 varchar(10)
)

INSERT INTO @myTable 
SELECT	'AA1|BBB1|CCCC1|DDD1|EEEE1', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAA2|BBB2|CC2|DDD2|EEEE2', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAAAA3|BBB3|CCCC3|DDD3|EEEE3', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAA4|BBB4|CCCC4|DD4|EEEE4', Null,Null,Null,Null,Null UNION ALL
SELECT	'A5|BBB5|CCCC5|DDD5|E5', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAA6|BBB6|CCCC6|DDD6|EEEE6', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAA7|BBB7|CCCCC7|DDD7|EEEE7', Null,Null,Null,Null,Null UNION ALL
SELECT	'AA8|BBBBBBB8|CCCC8|DDD8|EEEE8', Null,Null,Null,Null,Null UNION ALL
SELECT	'AA9|BBBBBBB9|CCCC9|DDD9|', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAA|BBBBBBBA|CCCCA|DDDA', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAB|BBBBBBBB|CCCCB|', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAC|BBBBBBBC|CCCCC', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAD|BBBBBBBD|', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAE|BBBBBBBE', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAF|', Null,Null,Null,Null,Null UNION ALL
SELECT	'AAG', Null,Null,Null,Null,Null

DECLARE	@I1	int,
	@I2	int,
	@I3	int,
	@I4	int,
	@I5	int

UPDATE U
SET
	  @I1 = CHARINDEX('|', MyCol + '|')
	, [MyCol1] = LEFT(MyCol, @I1-1)
	, @I2 =  NullIf(CHARINDEX('|', MyCol + '|', @I1+1), 0)
	, [MyCol2] = SUBSTRING(MyCol, @I1+1, @I2-@I1-1)
	, @I3 =  NullIf(CHARINDEX('|', MyCol + '|', @I2+1), 0)
	, [MyCol3] = SUBSTRING(MyCol, @I2+1, @I3-@I2-1)
	, @I4 =  NullIf(CHARINDEX('|', MyCol + '|', @I3+1), 0)
	, [MyCol4] = SUBSTRING(MyCol, @I3+1, @I4-@I3-1)
	, @I5 =  NullIf(CHARINDEX('|', MyCol + '|', @I4+1), 0)
	, [MyCol5] = SUBSTRING(MyCol, @I4+1, @I5-@I4-1)
FROM	@MyTable AS U

SELECT	*
FROM	@MyTable

Kristen

Edited by - Kristen on 11/28/2005 02:14:16
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 11/27/2005 :  15:41:57  Show Profile  Reply with Quote
Kristen! You just popped my brain circuit :) Too complicated for me :( But at least my problem is fixed and I have learned new tricks :) :)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 11/28/2005 :  00:14:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Good Stuff Kristen

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/28/2005 :  02:15:32  Show Profile  Reply with Quote
"You just popped my brain circuit"

Well ... you don't actually need to understand it!

Just change the parameters and run the first bit - it generates the code for your UPDATE (or SELECT)

Kristen
Go to Top of Page
Page: of 2 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.12 seconds. Powered By: Snitz Forums 2000