February 9, 2012 at 10:20 am
I have the following SQL parse Function:
USE [JobPortalIAN]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 02/09/2012 12:10:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
I am using the following SQL code to call the function.
select top 5 p.id,
(select top 1 * from dbo.split((select top 1 jobcategories1 from Profiles p inner join AllJobCategories j on j.UniqueId = p.Id), '('))
from Profiles p
I get the following results:
601803B6-2850-43F7-B5BC-000000FE9E4EFinance
549D0654-37CC-4E03-8278-000001CCD306Finance
AD9771BF-2E3F-4DB5-BAF0-000006640D74Finance
817061ED-3962-4153-B6A2-000006C5B1F0Finance
35D2C686-EFF6-4FF2-B7A9-00000AA82861Finance
The problem is that i always get the same job category, 'Finance'.
I am trying to get other categories as well, it seems to be stuck on the first one, which i assume is due to the Select TOP 1.
How do i get to the other categories? Do i need a loop, or something else?
Thanks
February 9, 2012 at 10:38 am
Rather than fuss with your splitter code why not use an excellent splitter developed by Jeff Moden and tested by many and know to work.
May I suggest reading this:
February 9, 2012 at 11:56 am
Thanks, that worked great!
February 9, 2012 at 12:56 pm
Jeff's parse code works great as a 'stand alone' query, but when it is part of another Stored Procedure, running dynamic SQL, it takes forever!
Is it not meant to work in this way?
Thanks
February 9, 2012 at 1:23 pm
My guess is you have something else in your code that is running slow. I have used his parsing function in all sorts of ways and places and have never had any kind of performance issues with it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2012 at 1:31 pm
Sean Lange (2/9/2012)
My guess is you have something else in your code that is running slow. I have used his parsing function in all sorts of ways and places and have never had any kind of performance issues with it.
+1
February 9, 2012 at 1:43 pm
You guys are correct!
I removed a superfluous Join statement, ran an "Actual Execution Plan', it suggested adding a new index and voila...... one second query 🙂
Thanks
February 9, 2012 at 5:40 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply