Avatar of terrydoll
terrydoll
 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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
terrydoll

8/22/2022 - Mon
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..
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck