March 27, 2003 at 2:59 am
My current position is in "Reporting" and this requires me to develop complex reporting solutions against a large and modular built database based in either Oracle or SQL depending on the clients preferences. I use many tools to do this but am often required to write functions and views on the Server in order to facilitate this.
I have written an Oracle function that will return the requested part of a cocatenated string (Why??? Because the database was created and controlled by developers and it seemed like a good idea to store several keys in one field) but am having extreme difficulty working out how to do this on SQL7. In fact it seems that SQL7 does not support UDF's at all. For SQL2000, How does T-SQL deal the IF,THEN,ELSE? and I will have to find an alternative to Oracle's INSTR function which allows me to feed the starting position so that I can work my way along the String.
Any help would be appreciated 🙂
create or replace function FN_DECONCAT
(STR_SOURCE in VARCHAR2, INT_FIELDNUM in INTEGER)
RETURN VARCHAR2 AS
STR_RETURN VARCHAR2(50);INT_COMMA1 INTEGER;INT_COMMA2 INTEGER;
/*
Purpose: To provide a method of returning the component parts of a comma separated, concatenated field.
An example is ADDITIONAL.ADD_PK.
It is passed the concatenated field, and the number of the component part required.
It returns the component part, if it exists, or NULL, if it doesn't. This state depends on the
presence of the comma that would precede it. How much of the source to return is governed
by the position/presence of the comma that would follow it.
Prerequisites: None.
*/
begin
if INT_FIELDNUM = 1 then
INT_COMMA1 := 0;
INT_COMMA2 := INSTR(STR_SOURCE,',',1,1);
if INT_COMMA2 = 0 then
STR_RETURN := RTRIM(STR_SOURCE);
else
STR_RETURN := SUBSTR(STR_SOURCE,1,INT_COMMA2-1);
end if;
else
INT_COMMA1 := INSTR(STR_SOURCE,',',1,INT_FIELDNUM-1);
INT_COMMA2 := INSTR(STR_SOURCE,',',1,INT_FIELDNUM);
if INT_COMMA1 = 0 then
STR_RETURN := '';
else
if INT_COMMA2 = 0 then
STR_RETURN := SUBSTR(RTRIM(STR_SOURCE),INT_COMMA1+1);
else
STR_RETURN := SUBSTR(STR_SOURCE,INT_COMMA1+1,INT_COMMA2-(INT_COMMA1 + 1));
end if;
end if;
end if;
This would be called by a simple...
Select FN_DECONCAT(<<STRINGFIELD>>,2) etc
...to return the value held between commas 2 and three if exists.
I'm one of those bad things that happen to good people 😀
March 27, 2003 at 3:21 am
Michael,
Try this:
CREATE PROCEDURE [dbo].[charlist]
@field_value varchar(4000), @delimeter char(1) =',', @sequence int, @output_val varchar(255) OUTPUT
AS
declare
@position smallint, @tempvar varchar(4000), @counter int, @continue char(1)
set @continue = 'Y'
set @counter = 0
set @output_val = ''
set @field_value = @field_value + @delimeter
while @continue = 'Y' begin
set @counter = @counter + 1
set @continue = 'N'
set @position = charindex(@delimeter,@field_value)
if @position > 0 begin
set @continue = 'Y'
set @tempvar = substring(@field_value,1,@position-1)
if @tempvar > '' and @counter = @sequence begin
set @output_val = @tempvar
return
end
select @tempvar = ltrim(stuff(@field_value,1,@position,''))
select @field_value = @tempvar
end
end
GO
Call the procedure by:
declare @string varchar(255)
exec charlist 'a,b,c,d,e', ',', 3, @string output
print @string
This would return the value: c
This loops round and extracts each value between delimiters and if it is the nth value in the list which you supply then it returns that value and exits. I have set this up to take variable delimieters but you could take this out if you want to.
Jeremy
March 27, 2003 at 3:36 am
Ahh so a SQL Procedure can accept and return values. This looks excellent but can you confirm that I can call this from a select query. Basically I often create a view of the table in question with all of the component parts of the string field split out as PART1, PART2 etc so that I can use these keys in a joining strategy. Something like...
Select charlist(<<STRINGFIELD>>, ',', 3,@string output) as PART3 FROM <<TABLE>>)
I'm one of those bad things that happen to good people 😀
March 27, 2003 at 3:37 am
Ahh so a SQL Procedure can accept and return values. This looks excellent but can you confirm that I can call this from a select query. Basically I often create a view of the table in question with all of the component parts of the string field split out as PART1, PART2 etc so that I can use these keys in a joining strategy. Something like...
Select charlist(<<STRINGFIELD>>, ',', 3,@string output) as PART3 FROM <<TABLE>>)
I'm one of those bad things that happen to good people 😀
March 27, 2003 at 3:58 am
No, you could not use this, as it stands, in a select statement.
It might be possible to create a user defined function to do this but I'm not too sure. I'll have a go and let you know how I get on.
March 27, 2003 at 4:05 am
That's very kind of you. If you have any Oracle SQL, Microsoft Access/Office, VB or Cognos Impromptu questions then I would be happy to reciprocate.
I'm one of those bad things that happen to good people 😀
March 27, 2003 at 4:22 am
This should work but no guarantees on performance:
CREATE FUNCTION fn_charlist (@str varchar(200), @delimeter char(1) =',', @sequence int)
RETURNS varchar(200)
AS
BEGIN
declare @position smallint, @tempvar varchar(4000), @counter int, @continue char(1), @output_val varchar(255)
DECLARE @sub varchar(200)
set @continue = 'Y'
set @counter = 0
while @continue = 'Y' begin
set @counter = @counter + 1
set @continue = 'Y'
set @position = charindex(@delimeter,@str)
if @position > 0 begin
set @continue = 'Y'
set @tempvar = substring(@str,1,@position-1)
if @tempvar > '' and @counter = @sequence begin
set @output_val = @tempvar
set @continue = 'N'
end
else begin
select @tempvar = ltrim(stuff(@str,1,@position,''))
select @STR = @tempvar
end
end
end
return (@output_val)
END
You can use this in selects:
SELECT dbo.fn_charlist('this,is,a,test',',',4) as 'Col'
Jeremy
March 31, 2003 at 1:40 am
I'll break the Function down and run some tests. Many Thanks tho for your efforts.
I'm one of those bad things that happen to good people 😀
I'm one of those bad things that happen to good people 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply