October 7, 2011 at 8:45 am
Hi everybody,
I'm searching for a simple solution (no store-proc) for such a simple (or so I think) problem.
I have some field named GL_GETLOST which contains this kind of strings : "19950327;CF;001;02;2345"
All I want is to access at the separated strings in some SELECT statement giving the fact that the length of the strings between the ";" are not fixed. 😀
Any help welcome and kindly appreciated.
Regards,
October 7, 2011 at 10:33 am
GJ-238291 (10/7/2011)
Hi everybody,I'm searching for a simple solution (no store-proc) for such a simple (or so I think) problem.
I have some field named GL_GETLOST which contains this kind of strings : "19950327;CF;001;02;2345"
All I want is to access at the separated strings in some SELECT statement giving the fact that the length of the strings between the ";" are not fixed. 😀
Any help welcome and kindly appreciated.
Regards,
I picked up a function called fn_split a number of years ago that will split a string via a delimiter and return a table you can join to.
Here is the code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_Split]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function dbo.fn_Split(@string varchar(max),@delimiter varchar(20) = ',')
returns
@array table(idx smallint primary key,value varchar(max))
as
/****************************************************************************************************
Arguments:@string := string to parsed/split
@delimiter := string used to split the source stringlist.
Returns:@array (table)
Calls:None.
Tables:None.
Description:Split a delimited string into a table containing an index column and adata column.
Emulatesvarious implementations of a Split() function in programming languages.
If no delimeter is specified (@delimiter is null), the string is splitcharacter by character.
Assumes no delimiter at end of source string!!
Revision History:<# v0.0.1 #> 06/04/2002 KJ
Initial build.
*****************************************************************************************************/
begin
/** variable declaration **/
declare
@idx smallint, -- index for array
@value varchar(8000), -- data for array
@strike smallint, -- number of characters to consume fromthe source string
@len_delim tinyint -- length of the delimiter string
/** init **/
select @idx = 0,
@string = ltrim(rtrim(@string)),
@len_delim = datalength(@delimiter)
/** check for empty delimiter **/
if not ((@len_delim = 0) or (@delimiter is null))
begin
/** if you can find the delimiter in the text, retrieve the first elementandinsert it with its index
into the return table.**/
while charindex(@delimiter, @string) > 0
begin
set @value = substring(@string, 1, charindex(@delimiter,@string) - 1)
insert @array(idx,value)values(@idx,@value)
/** trim the element and its delimiter from the front of the string.increment the index and loop.**/
select @strike = datalength(@value) + @len_delim,
@idx = @idx + 1,
@string = ltrim(right(@string, datalength(@string) -@strike))
end /** while charindex(@delimiter, @string) > 0 **/
/** if you can’t find the delimiter in the text, @string is the lastvalue in@array.**/
set @value = @string
insert @array(idx,value)
values(@idx,@value) end
else
begin
/** if the delimiter is an empty string, check for remaining textinstead of a delimiter. insert the
first character into thearray table. trim the character from the front of the string.increment the
index and loop.**/
while datalength(@string) > 1
begin
set @value = substring(@string, 1, 1)
insert @array(idx, value)
values(@idx, @value)
select @idx = @idx + 1,
@string = substring(@string, 2, datalength(@string) - 1)
end /** one character remains. insert the character, and exit the whileloop. **/
insert @array(idx, value)
values(@idx, @string)
end /* while datalength(@string) > 1 */
/* end if ((@len_delim = 0) or (@delimiter is null)) */
return
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
October 7, 2011 at 11:24 am
Try the function by Jeff Moden that uses a Tally Table, much cleaner and faster.
October 7, 2011 at 11:31 am
venoym (10/7/2011)
Try the function by Jeff Moden that uses a Tally Table, much cleaner and faster.
That article just talks about tally. This one has a function that can be used:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply