Split field with seperator

[talon121]

Trying to split a field.

ie

[Field1]
Sample The dog jumped the fence

Example output:

Field(1) + 'asd ' + Expr(2)
The asd dog

Not sure if what I typed is correct.. Hopefully it helps. Trying to take one field, and break into array so I can build a new output in another field.

 

[markros]

Check

http://berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split

and links from it

PluralSight Learning Library

 

[talon121]

Thanks, but when I attempt calling it..
I get error.

SELECT FileNameaa AS Expr1, Subjectaa AS Expr2, dbo.ufn_Alines(Expr2, SPACE(1)) AS Expr3

Is what im using..

But I get the error that Alines is missing or ambiguous. (Am I calling it right?)

 

[markros]

No, you're not calling it right.

First of all, did you create that function in the right database?

Second, since it's a table valued function, you need to call it like this (SQL Server 2005+):

select FileName as Expr1, SubjectArea as Expr2, F.*
from YourTable
CROSS APPLY dbo.ufn_Alines(SubjectAread, space(1)) F

PluralSight Learning Library

 

[talon121]

Any idea in SQL Express 2005 (9.0.4053)?

says "The CROSS APPLY SQL construct or statement is not supported".

Any idea from there?

 

[gmmastros]

It sounds to me like you could be running in to a compatibility level problem.

I suggest you download and install a free tool that I wrote called SQLCop (

http://sqlcop.lessthandot.com).

When you run this tool, log in to your database and then expand the Configuration node, and then expand "Compatibility Level". If the compatibility level of your database does not match the compatibility level of the SQL Server instance, you'll know it. You will also see a wiki article that explains how to change the compatibility level.

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