Converting a function in SQL Server into Teradata function.

Database
Highlighted
Teradata Employee

Converting a function in SQL Server into Teradata function.

Hello Experts,

I have SQL server function as below and is called wthin a view, can we implement similar logic in a function in Teradata? If not then is there any alternative?

 

CREATE FUNCTION [dbo].[SplitStrings] (
@String varchar(8000)
,@delimiter varchar(1)
)

RETURNS @T TABLE (Value INT, ID INT)

AS
BEGIN
--IF substring(@String,1,1)=';'
IF substring(@String,1,1)=@delimiter
SET @String=substring(@String,2,8000)


--SET @String = @String + ';'
SET @String = @String + @delimiter


declare @Pos int
declare @Value int
declare @s varchar(20)
declare @long int

set @long = 1

WHILE len(@String) <> 0
BEGIN
set @Pos = charindex(@delimiter,@String)

IF @Pos=0 BREAK
set @s = substring(@String,1,@Pos-1)

-- insure we actually have a value before we insert in the table
if ltrim(rtrim(@s)) <> ''
INSERT @T (Value, ID) VALUES (cast(@s as int), @long)

SET @String = substring(@String,@Pos+1,8000)
SET @long = @long+1

END

RETURN
END
--------------------------------------------------------------------------------------------------------

GO

 


Accepted Solutions
Junior Contributor

Re: Converting a function in SQL Server into Teradata function.

You don't need a UDF for this, have a look at the STRTOK_SPLIT_TO_TABLE function.

 

 

1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: Converting a function in SQL Server into Teradata function.

You don't need a UDF for this, have a look at the STRTOK_SPLIT_TO_TABLE function.

 

 

Teradata Employee

Re: Converting a function in SQL Server into Teradata function.

Thank you very much Dieter, it worked like charm.

Teradata Employee

Re: Converting a function in SQL Server into Teradata function.

That's a handy function!  Functions like this are documented in the Teradata SQL Reference: Functions and Operators.  In general, if you can't find another way to convert a SQL (or PL/SQL) function to Teradata, check out

http://developer.teradata.com/blog/georgecoleman/2012/03/derived-tables-with-ordered-analytical-func...

and

http://developer.teradata.com/blog/georgecoleman/2014/01/ordered-analytical-functions-translating-sq...