TSQL Split function

Hello Experts,
I am using SQL Server 2008:

I want a write a SQL function that will split the following string  by backslash delimiter and return a table..

Input String:   CNCS01306/01307  ( the string format and length remains the same)

Output:
CNCS01306
CNCS01307

Any help?


Thanks
LVL 8
mani_saiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
A very efficient, probably "best practice", splitter is dbo.DelimitedSplit8K.  You can Google for the source.
0
mani_saiAuthor Commented:
not just splitting, I want the second string (added with first strings first 4 characters as shown in my example...)
0
Scott PletcherSenior DBACommented:
Nah, still basically just a split, you just have to get the prefix first, then split the string minus the prefix, and add the prefix back to the front of every item afterward :) :

DECLARE @string varchar(8000)
SET @string = 'CNCS01306/01307/1309/241/99908765'

SELECT ca1.string_prefix + ds.Item
FROM (
    SELECT @string AS string
) AS base_data
CROSS APPLY (
    SELECT LEFT(string, PATINDEX('%[0-9]%', string) - 1) AS string_prefix
) AS ca1
CROSS APPLY dbo.DelimitedSplit8K (SUBSTRING(string, LEN(string_prefix) + 1, 8000), '/') ds
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ManjuIT - Project ManagerCommented:
If the format and length remains same, why can't you use simple left function?

Left(columnname,9)
0
ManjuIT - Project ManagerCommented:
My bad, ignore my previous comment
0
Chris LuttrellSenior Database ArchitectCommented:
Is something like this what you are looking for?  If like you said "( the string format and length remains the same)"
CREATE TABLE test (id INT, val VARCHAR(20));
INSERT INTO dbo.test ( id, val )
VALUES  (1, 'CNCS01306/01307 ' ), (2, 'CNCS01308/01309' );
GO

ALTER FUNCTION dbo.fnSimpleSplit (@input VARCHAR(200))
RETURNS TABLE
AS
RETURN (
SELECT SUBSTRING(@input,1,9) NewVal
UNION
SELECT SUBSTRING(@input,1,4)+SUBSTRING(@input,11,5) 
);
GO

SELECT T.id, NewVal
FROM dbo.test T
CROSS APPLY dbo.fnSimpleSplit(val) x

DROP TABLE dbo.test;

Open in new window

Returns data like this for the 2 records I put in the test table:
Query Results
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
check this. If this works for you, you can create a function and with this logic and call it in your code.
declare @str varchar(100) = 'CNCS01306/01307'

select case when n > 1 then left(string,4) else '' end + ltrim(SUBSTRING(string, n, CHARINDEX('/', string + '/',n) - n)) split_string
  from (select @str string) t1
  CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
 WHERE SUBSTRING('/' + string, n, 1) = '/'

Open in new window

0
Pawan KumarDatabase ExpertCommented:
@Author - Are you satisfied with the above solutions ? or you need some more info.
0
Pawan KumarDatabase ExpertCommented:
Closing since the author has not returned.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.