SPLIT() Function in SQL to Send values to a Table along with ID from within SELECT Statement? 1

[kjv1611]

I am probably not describing this very well, but here goes.

I want to find a way to basically use something like the VBA Split() function that will split out values to an array, but I like the idea of just splitting out to a table.

So assuming I start with something like this:

ID[TAB][TAB]Description
1234[TAB]'One. Two. Three. Four. Five. '
2345[TAB]'Red. Green. Orange. Five. Four. '

And I want to end up with:

ID[TAB][TAB]NewValue
1234[TAB]'One'
1234[TAB]'Two'
1234[TAB]'Three'
1234[TAB]'Four'
1234[TAB]'Five'
2345[TAB]'Red'
2345[TAB]'Green'
2345[TAB]'Orange'
2345[TAB]'Five'
2345[TAB]'Four'

I've tested with and attempted to slightly modify the function found here:

http://stackoverflow.com/questions/...ng-and-insert-values-into-table-in-sql-server

It's the function posted by M.Ali on Nov 10, 2013.

So I tried to change it to where it will accept and return the ID field like so:

USE [Sandbox]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [Split] (
      @InputString                  VARCHAR(8000),
      @Delimiter                    VARCHAR(50),
      [highlight #FCE94F]@ID							DECIMAL(25,0)[/highlight]
)

RETURNS @Items TABLE (
      [highlight #FCE94F]ID							DECIMAL(25,0),[/highlight]
      Item                          VARCHAR(8000)
)

AS

BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
            
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

      DECLARE @Item                 VARCHAR(8000)
      DECLARE @ItemList       VARCHAR(8000)
      DECLARE @DelimIndex     INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@ID, @Item)

            -- Set @ItemList = @ItemList minus one less item
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES ([highlight #FCE94F]@ID[/highlight], @Item)
      END

      -- No delimiters were encountered in @InputString, so just return @InputString
      ELSE INSERT INTO @Items VALUES ([highlight #FCE94F]@ID[/highlight] ,@InputString)

      RETURN

END -- End Function

And then I can't seem to quite fit that into a query to toss those into a temp table for review. Maybe I'm using the wrong method or wrong type of function for my purpose? Or am I just doing it wrong?

Here's the latest SELECT statement I tried:

SELECT * INTO #HistDetails
FROM	Split(
	SELECT	h.hID ,h.HistoryDesc ,'.  '
	FROM	#LongHist h WITH (NOLOCK)
	) x

Thanks for any suggestions,



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[gmmastros]

Here's how I would have approached this...

First, I have a split function that I have had in my database for a million years. It looks like this:

ALTER Function [dbo].[Split](
    @CommaDelimitedFieldNames Varchar(Max), 
    @CharToFind VarChar(10) 
    ) 
Returns @Tbl_FieldNames 
    Table (Position Integer Identity(1,1), FieldName VarChar(8000)) 
As 
  Begin 

    Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @CharToFind
    Declare @Pos1 Int
    Declare @pos2 Int

    Set @Pos1=1
    Set @Pos2=1
    While @Pos1<Len(@CommaDelimitedFieldNames)
      Begin
        Set @Pos1 = CharIndex(@CharToFind, @CommaDelimitedFieldNames,@Pos1)
        
        Insert @Tbl_FieldNames 
        Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(8000))
        
        Set @Pos2=@Pos1+len(@CharToFind)
        Set @Pos1 = @Pos1+Len(@CharToFind)
      End 
    Return
  End

Then, your query would look like this...

Select  Id, LTrim(RTrim(FieldName))
From    #HistDetails
        Cross Apply dbo.Split(#HistDetails.Data, '.') As Data
Where   Data.FieldName > ''

The cross apply allows the function to get executed once for each row in the main table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kjv1611]

THANK YOU THANK YOU THANK YOU ...

THANK YOU THANK YOU...

THANK YOU!



I'll try this right now - that sounds exactly like what I was trying to do, but just where it will actually work! I'll post back ASAP with progress.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Ok, so now I have a clue as to how to use CROSS APPLY. Historically, I've not known of a reason to use it, and didn't bother. But this is definitely a reason to use it for SURE!!

Thanks again. I'd throw 5 stars at that instead of one if I could. I believe this is going to help with more than one project that I'll eventually have to hit on. And more than that, just having a way to understand how CROSS APPLY even works in the first place is a big deal to em personally.

Thanks again!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[gmmastros]

glad I could help.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom