Solved

TSQL Split function

Posted on 2016-08-03
9
68 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 69

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 69

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 6

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 6

Expert Comment

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

Accepted Solution

by:
Chris Luttrell earned 500 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 40

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 28

Expert Comment

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

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

821 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