Hi:

I have a UDF that splits a string based on a delimiter.
The UDF returns a table of records:
1- ID
2- Value

The string I am sending to split is:
'2-4-07:00 AM-08:30 AM'

I am splitting on '-'
What I get back is a table of 4 rows.
I want to get each value, convert it to the right data type, then assign it to its own variable.
How can I get each row alone manually? Can I? Without using Cursors?
if I do a "select * from Split(@myData,'-')", then I cannot retrieve each field alone,

Thank you.

SomeNewTricks2 wrote:

if I do a "select * from Split(@myData,'-')", then I cannot retrieve each field alone,

select
    A.ID,
    A.Value,
    CASE A.ID
        WHEN 1 THEN CAST(A.Value) AS Whatever
        WHEN 2 THEN CAST(A.Value) AS Whatever
        WHEN 3 THEN CAST(A.Value) AS Whatever
        WHEN 4 THEN CAST(A.Value) AS Whatever
    END
from
    Split(@myData,'-') AS A

So my first try was pretty far off.  Sorry :-)

And here's a suggestion which works, but boy is it ugly.  I have a feeling someone is going to come along and give you a much more elegant idea.

DECLARE @TEMP TABLE (splitID int, splitValue varchar(100), ID int, Age int, timeFrom datetime, timeTo datetime)
DECLARE @ID int, @age int, @timeFrom datetime, @timeTo datetime

INSERT INTO
 @TEMP
(
 splitID,
 splitValue,
 ID,
 Age,
 timeFrom,
 timeTo
)
SELECT
 ID,
 Value,
        CASE WHEN A.ID = 1 THEN CAST(A.Value AS int) ELSE NULL END AS ID,
        CASE WHEN A.ID = 2 THEN CAST(A.Value AS int) ELSE NULL END AS AGE,
        CASE WHEN A.ID = 3 THEN CAST(A.Value AS datetime) ELSE NULL END AS timeFrom,
        CASE WHEN A.ID = 4 THEN CAST(A.Value AS datetime) ELSE NULL END AS timeTo
FROM
        split('2-4-07:00 AM-08:30 AM','-') AS A

SELECT @ID = ID FROM @TEMP WHERE splitID = 1
SELECT @Age = Age FROM @TEMP WHERE splitID = 2
SELECT @timeFrom = timeFrom FROM @TEMP WHERE splitID = 3
SELECT @timeTo = timeTo FROM @TEMP WHERE splitID = 4

I have my own UDF called fnSplitAt which I use for this sort of functionality.  A solution with this function would be simpler:
SELECT
   @ID = CAST(dbo.fnSplitAt('2-4-07:00 AM-08:30 AM','-',1) AS int),
   @Age = CAST(dbo.fnSplitAt('2-4-07:00 AM-08:30 AM','-',2) AS int), 
   @timeFrom = CAST(dbo.fnSplitAt('2-4-07:00 AM-08:30 AM','-',3) AS datetime),
   @timeTo = CAST( dbo.fnSplitAt('2-4-07:00 AM-08:30 AM','-',4) AS datetime)

Alternately, why don't you just split up the string on the front end?  It seems a waste to have SQL Server doing this string function.

Hi:
Sorry for intercepting in this thread, I once ran into such a solution.
I believe our friend SomeNewTricks2, is trying to send a block of data to the SP.
I had once to do so, so that I don't do multiple inserts for the same UserID, so I concated the user records and sent them as one block.

I do vote for Terri's second approach, and if you are interested in having such a function you can try the following:

CREATE FUNCTION dbo.fn_SplitAt
(
 @List NVARCHAR(2000),
 @SplitOn NVARCHAR(5),
 @Position INT
)
 
RETURNS VARCHAR(100)
 
AS 
 BEGIN
  DECLARE @IPosition INT
  DECLARE @RtnValue VARCHAR(100)
  SET @IPosition  = 1

  -- While a delimiter is found in the original list
  WHILE ( CHARINDEX(@SplitOn,@List) > 0 )
  BEGIN

   -- If the position of the required item equals to the counter of the current item
   IF ( @IPosition = @Position )   
   BEGIN
    -- Get that data
    SET  @RtnValue =  ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
   END

   -- Incrememt the cursor
   SET @IPosition = @IPosition + 1

   -- Remove from the original list the already checked item
   Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
  END

  -- We might be asking the last item in the original string
  IF ( @RtnValue IS NULL )
   SET @RtnValue = @List

  RETURN (@RtnValue)
 
 END

The above function I once used it from the 4GuysFromRolla.com website, originally, it only splits a string based on a delimiter, but what I did is I made it return only a specific item in the given string based on the position specified, which is an input too.

Hope it helps,

Thank you Terri for such a great solution, they make one have a wide understanding of what one can do in SQL Server.

Regards

haidar_bilal wrote:

I do vote for Terri's second approach, and if you are interested in having such a function you can try the following: CREATE FUNCTION dbo.fn_SplitAt The above function I once used it from the 4GuysFromRolla.com website, originally, it only splits a string based on a delimiter, but what I did is I made it return only a specific item in the given string based on the position specified, which is an input too.