Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

TSQL Split function

Posted on 2016-08-03
9
Medium Priority
?
119 Views
Last Modified: 2016-10-05
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
0
Comment
Question by:mani_sai
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 41741208
A very efficient, probably "best practice", splitter is dbo.DelimitedSplit8K.  You can Google for the source.
0
 
LVL 8

Author Comment

by:mani_sai
ID: 41741218
not just splitting, I want the second string (added with first strings first 4 characters as shown in my example...)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 41741468
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:Manju
ID: 41741726
If the format and length remains same, why can't you use simple left function?

Left(columnname,9)
0
 
LVL 7

Expert Comment

by:Manju
ID: 41741727
My bad, ignore my previous comment
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points (awarded by participants)
ID: 41741782
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
 
LVL 41

Expert Comment

by:Sharath
ID: 41741893
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
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41762008
@Author - Are you satisfied with the above solutions ? or you need some more info.
0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41829493
Closing since the author has not returned.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question