asked on

SQL Function Help

I have a comma delimited list of values which may be different for each record. I would like to change those values to different values. For example, if the value is 1,2, then I would want the value returned to be 2,55.

I am new to writing SQL functions and not sure of the proper SQL syntax. Below is a rough example of how I think it should work, but it does not.

CREATE FUNCTION getServices
( @Val1 nvarchar(50))

RETURNS nvarchar(50)
AS
BEGIN
      DECLARE @tempServices nvarchar(50);
      CASE WHEN CHARINDEX(',1,', ',' + @Val1 + ',') > 0 THEN @tempServices = @tempServices + ',' + 2
      CASE WHEN CHARINDEX(',2,', ',' + @Val1 + ',') > 0 THEN @tempServices = @tempServices + ',' + 55

RETURN (@tempServices)
END
go

SELECT getServices(services) AS t
FROM pff

TIA,

Terry

Om Prakash

You can use any of the split function to split the data in temp table from the following link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Once this is done you will the data in temp table. Use this table to convert into whatever you want using case statement.

Anuradha Goli

CREATE 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

Open in new window

Declare @data varchar(300)
 SET @data = '234234.24;234.54;23443.75'
 select * FROM [dbo].[Split](@data,';')

Open in new window

terrydoll

ASKER

Thanks for the responses and the ideas to use split functions.. At this point, I am only interested in getting my function to work.. I just need a little help in the proper syntax for the above function to work..

Guy Hengel [angelIII / a3]

so, you want something like this?

CREATE FUNCTION getServices
( @Val1 nvarchar(50))

RETURNS nvarchar(50)
AS
BEGIN
      set @Val1 = ',' + @Val1 + ','
      set @Val1 = replace(@Val1, ',2,', ',55,')
      set @Val1 = replace(@Val1, ',1,', ',2,')
      set @Val1 = substring(Val1 , 2, len(Val1) -2 )
     RETURN (@Val1 )
END

Open in new window

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

terrydoll

ASKER

Trying to test function, but not sure how to install it in MS SQL Server Management Studio. I save the file as a scalar-valued function, but not working. I checked this link:

http://social.msdn.microsoft.com/Forums/en/sqltools/thread/898f66f9-fd13-45a5-a019-cde1e0512d03

And they state:

"You have to execute the SQL create statement in your text file to install it on the server."

How would I do this with the above function examples?

Qlemo

Put the text as-is into a "New Query" window connected to the correct database. If you do not have that function defined already, replace the "ALTER" by "CREATE". After executing, you have the function defined and tested.

terrydoll

ASKER

Thank you - that worked, but now I am getting this error when I try to test it with:

SELECT getServices(services) AS t
FROM pff

Error:
'getServices' is not a recognized built-in function name.

Qlemo

That is a strange one, but for some reasons you have to add the schema name to the function when calling it:
select dbo.getServices(services) as t from pff

You can see which schema to use by looking into the list of functions in Management Studio; probably it says "dbo.geServices".

terrydoll

ASKER

Thanks for everyone's comments and suggestions!