Delimited Fields

[m6s]

I have a problem that I had once solved with C#.
I have a field with multiple values like, a|b|c|d|e
a,b,c etc are values from some web form. They get apart from the code with guide the | symbol.

Can I make this functionality in SQL? Should I use procedure, or the CLR for SQL2005 ?

Has anyone seen anything like that?

Thank you in advance...

[iburyak]

If you willing to use function created by me, you can pass this parameter to a function and it will return you a table each value in a separate row the same way you create collection in C++.

1. Create function on the server:

CREATE FUNCTION Split(@String nvarchar(4000), @Delimiter char(1))      
RETURNS @Results TABLE (ID int, Items nvarchar(4000))      
AS      
 
BEGIN      
    DECLARE @INDEX INT      
    DECLARE @SLICE nvarchar(4000)      
    DECLARE @ID int  
 
    SELECT @INDEX = 1, @ID = 1      
    WHILE @INDEX !=0      
 
 
        BEGIN       
         -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER      
         SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)      
         -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE      
         IF @INDEX !=0      
          SELECT @SLICE = LEFT(@STRING,@INDEX - 1)      
         ELSE      
          SELECT @SLICE = @STRING      
         -- PUT THE ITEM INTO THE RESULTS SET      
         INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)     
         SELECT @ID = @ID + 1   
         -- CHOP THE ITEM REMOVED OFF THE MAIN STRING      
         SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)      
         -- BREAK OUT IF WE ARE DONE      
         IF LEN(@STRING) = 0 BREAK      
        END      
    RETURN      
END      
 
 
 
 

2. execute following statement:

select * from Split('a|b|c|d|e', '|')

Good Luck.

[m6s]

On one hand, it worked the function. For that thaaank you!
On the other hand, it seems I have some problem with who has credentials...

I have a login as 'm6s'
a user as 'm6s'
the schema is under dbo for ALL the tables in the database and other objects.

It seems that even if I do use the syntax [dbo].split, or [m6s].split the function is not recognised.

Also, this happens to even tempdb!!!!

Similar problem seems to be happen with assemblies. I found a regex tutorial and while built it and pluged it, it can not be found again. I say AUTHORIZATION dbo, but the properties are blank for who is the owner of this assembly too.

Conclusion in sql express i have 'm6s' as login and user in the logins and users, is something more to do so to see the function? or the assembly?

Bottom line, thank you very much for your snippet.

[iburyak]

You probably have some default Schema.

Try executing:

sp_help split 

good luck.

[m6s]

Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
SplitX dbo table function 2007-10-10 22:49:31.123



Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
ID int no 4 10 0 yes (n/a) (n/a) NULL
Items nvarchar no 8000 yes (n/a) (n/a) SQL_Latin1_General_CP1253_CI_AI


Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
No identity column defined. NULL NULL NULL


RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.


Parameter_name Type Length Prec Scale Param_order Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------ ----------- ----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
@STRING nvarchar 8000 4000 NULL 1 Greek_CS_AS
@Delimiter char 1 1 NULL 2 Greek_CS_AS

[iburyak]

In your schemas do you have functions folder?

I am not sure how to help you here thou. Ask your DBA.

Irina.

[m6s]

The fun part is that when I run select split(...) all is cool
if I do select split(table.field) FROM table
I get the error...

Anyway, Irena, thank you very much for your code.
It is most helpful.
Dimitris