TSQL Split function

Mani Pazhana
Mani Pazhana used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
A very efficient, probably "best practice", splitter is dbo.DelimitedSplit8K.  You can Google for the source.

Author

Commented:
not just splitting, I want the second string (added with first strings first 4 characters as shown in my example...)
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ManjuIT - Project Manager

Commented:
If the format and length remains same, why can't you use simple left function?

Left(columnname,9)
ManjuIT - Project Manager

Commented:
My bad, ignore my previous comment
Senior Database Architect
Commented:
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
SharathData Engineer

Commented:
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

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@Author - Are you satisfied with the above solutions ? or you need some more info.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Closing since the author has not returned.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial